Dyanmic Date - Display last 5 workdays dynamically

[Anonymous]

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

[jdbuchanan71 · Accepted 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.

[Anonymous]

@jdbuchanan71Thank you!