Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
We are trying to accomplish a volume actual vs. goal dashboard for material bought and sold.
As part of the dashboard, the requirement is to also display the volume for each of the last 5 workdays, like below.
In our OLAP cube, we have monthly budget for volume, which I broke down into daily goals in Excel. Being a Power BI rookie, I did all of the data modeling and computations around workdays and daily goals in Excel, and used that file as the data source for Power BI.
The Excel file has a TODAY() function, which it uses to compute WORKDAY-5 and so on. Workdays are Monday through Friday.
However, each day that the Excel file gets refreshed, this above visual needs to be manually updated - i.e. I have to manually uncheck the workday-6 and check the most recent workday that got added to the dataset.
Doing manual refreshes is not feasible and we require a dynamic solution.
From looking for solutions in the community, it appears that I may need to build out a date dimension, and then a custom measure for computing workdays.
How do I go about accomplishing this dynamic last 5 workday measure?
Thanks!
Naseef
Solved! Go to Solution.
Hello @Anonymous
You are correct that a date table is your best bet to accomplish this. When you have one you can add a calculated column that will change as the date changes. You can either just show the date offset (difference between the date row and TODAY()) and use that as a filter (between -7 and -1 will give you the last 5 working days).
Or you would apply the logic in the calculated column so you only have to set the filter to a single value instead of between.
DayOffset = DATEDIFF ( TODAY(),Dates[Date],DAY)
Last5Working =
VAR _Days = DATEDIFF ( TODAY(),Dates[Date],DAY)
RETURN IF ( _Days >= -7 && _Days <= -1, "Last 5 working days")
I have attached my template file with the calculated columns for you to look at.
Hello @Anonymous
You are correct that a date table is your best bet to accomplish this. When you have one you can add a calculated column that will change as the date changes. You can either just show the date offset (difference between the date row and TODAY()) and use that as a filter (between -7 and -1 will give you the last 5 working days).
Or you would apply the logic in the calculated column so you only have to set the filter to a single value instead of between.
DayOffset = DATEDIFF ( TODAY(),Dates[Date],DAY)
Last5Working =
VAR _Days = DATEDIFF ( TODAY(),Dates[Date],DAY)
RETURN IF ( _Days >= -7 && _Days <= -1, "Last 5 working days")
I have attached my template file with the calculated columns for you to look at.
User | Count |
---|---|
77 | |
77 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |