Relationship between static values and date values (relationships between tables may be needed)

Hi, @

According to your description, I think you can use measure and slicer in Power BI to achieve your requirement, you can try this method:

1. Create a calender table to get the data across 5 years:

Date = CALENDAR(DATE(2021,1,1),DATE(2025,12,31))

2. Create these calculated columns in the date table:

Month = FORMAT([Date],"mmm")

Month-Year = [Month]&"-"&RIGHT(YEAR([Date]),2)

Month-Year1 = YEAR([Date])&FORMAT([Date],"mm")

Is Working Day =

IF(WEEKDAY([Date],2)>5,0,1)

Then sort the column [Month-Year] like this to make it ordered in the slicer:

3. Create a measure:

Sum of working days =

SUMX(ALLSELECTED('Date'),[Is Working Day])

4. Create a slicer to place [Month-Year] and a card chart to place the measure:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.