TheMacroNoob
Board Regular
- Joined
- Aug 5, 2022
- Messages
- 52
- Office Version
- 365
- Platform
- Windows
Hello Power BI experts.
I have a dashboard tracking hours on various days, among other data points. I am using a SUMMARIZE table function, grouped by the columns you can see in the code below. The only problem I have is that the ClinicHours table does not have any data prior to 1/1/2023. I have been asked to return the next closest ClinicHours[Hours] value by weekday, instead of using 8 as a default value in a future calculation.
How would I find the next closest weekday value? For example: If it is 1/15/2022, a Saturday, I would want to return the value of the closest available Saturday from ClinicHours[Hours].
I have a dashboard tracking hours on various days, among other data points. I am using a SUMMARIZE table function, grouped by the columns you can see in the code below. The only problem I have is that the ClinicHours table does not have any data prior to 1/1/2023. I have been asked to return the next closest ClinicHours[Hours] value by weekday, instead of using 8 as a default value in a future calculation.
How would I find the next closest weekday value? For example: If it is 1/15/2022, a Saturday, I would want to return the value of the closest available Saturday from ClinicHours[Hours].
Power Query:
Summary = SUMMARIZE(
ClosedClinics,
'Calendar'[Date],
LookupART[New Temp Names],
ClosedClinics[Status],
ClosedClinics[Reason],
"Hours Open",
SUM(ClinicHours[Hours])
)
