Date table - Aligning workdays over months

Jodallen123 — Fri, 22 Nov 2024 09:37:06 GMT

Hi,

I just got a request to build a date slicer that "aligns workdays over months", what I mean by this is that if 16 workdays has passed in the current month, the slicer should filter the date table to only show 16 work days in all the previous months as well. The purpose is to make it easier identifing trends in the sales data if you can easily see that "16 days in to May we sold x amount and now in November we sold y amount".

Another problem is of course that the number of days differ between months so somehow you need to incorporate the % of passed workdays as a share of total workdays that specific month.

It could be added directly in to the date table using m-code or with calculated columns, the main goal is to get the functionality that I am describing. Or maybe there are completely other ways to get the functionality I am after.

It would be a really good feature to have a slicer like this but I don't really know where to start with this task to be honest, I have searched around but I have not found any information about this. Has anyone else built something similar and/or have any input on how this can be done?

Thanks in advance!

Re: Date table - Aligning workdays over months

Poojara_D12 — Fri, 22 Nov 2024 10:24:43 GMT

Hi Jodallen123

To create a workday-aligned date slicer that works across months in Power BI, follow these steps:

Create a Date Table:
- Create a Date Table with all the dates needed, ensuring you have columns for Day of the Week, Month, Year, and Workday Flag (to indicate whether it's a workday or not).
Add Workday Column:
- Add a column to calculate whether a date is a workday (e.g., weekdays but excluding holidays).

WorkdayFlag = IF(WEEKDAY([Date], 2) <= 5, 1, 0) // Assumes weekends are non-workdays

Add Cumulative Workday Count:
- Add a cumulative count of workdays up to each date.

CumulativeWorkdays = CALCULATE(COUNTROWS('DateTable'), FILTER('DateTable', 'DateTable'[Date] <= EARLIER('DateTable'[Date]) && 'DateTable'[WorkdayFlag] = 1))

Calculate Total Workdays Per Month:
- Add a column to calculate the total workdays for each month.

TotalWorkdaysMonth = CALCULATE(COUNTROWS('DateTable'), FILTER('DateTable', 'DateTable'[Month] = EARLIER('DateTable'[Month]) && 'DateTable'[WorkdayFlag] = 1))

Percentage of Workdays Passed in Current Month:
- Create a column to calculate the percentage of workdays passed in the current month relative to the total workdays in the month.

WorkdaysPassedPercentage = 'DateTable'[CumulativeWorkdays] / 'DateTable'[TotalWorkdaysMonth]

Build the Slicer:
- Use the WorkdaysPassedPercentage column in the slicer to filter dates. When a user selects a number of workdays, the slicer should filter the data based on the relative percentage of workdays passed in each month.
Adjust the Data:
- You can also use this percentage logic to align sales or other KPIs by the number of workdays passed, helping to compare performance across months.

Summary:

Workday Calculation: Flag workdays and create cumulative workdays.
Month Alignment: Use total workdays in each month to calculate the percentage of workdays passed.
Slicer: Use a percentage-based slicer to align workdays across months and compare trends.

This setup allows you to filter data by workdays and easily compare the same number of workdays across different months.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

topic Re: Date table - Aligning workdays over months in DAX Commands and Tips

Date table - Aligning workdays over months

Re: Date table - Aligning workdays over months

Summary: