Working Day Calculations Selected Month

[Anonymous]

Hi,

I have several calculations based on the [working days for the current month] and the [number of working days in the month up until the current date]. I have a page where I am using a Month&Year slicer. The MTD calculations that I use display data for the current month as the working day measures are for the current month but when I select a different month, the calculations appear blank.

What kind of measure shall I implement where if the month other than the current month is selected, the use the total working days of the selected month in those calculations is used.

One of the calculations I am using is :

MTD Target = [Target Per Month]/[Total Working Days in Current Month])
   

And the total working days in current month calculation is :

Total Working Days in Current Month = 
CALCULATE(
    SUM('Date'[WorkingDay]),
    'Date'[Year] = YEAR(TODAY()) && 'Date'[MonthOfYear] = MONTH(TODAY())
)

I would want the MTD Target measure to look like:

MTD Target =

Var selected month = current month

RETURN

IF current month is selected then return

 ([Target Per Month]/[Total Working Days in Current Month])

otherwise return 

([Target Per Selected Month]/[Total Working Days for selected Month])

I have the following table and slicer below, for the current month it works but I need something for the selected month.

Can provide my file if required.

Any help would be appreciated, Thanks.

[speedramps · Accepted solution]

Hi again Anony51

As previouisly mentioned, twice It is best practice to use Calendar table for all Power BI date logic.

Don't attempt to create your own DAX date logic. Click here to begin free Calendar table training 

Have a calendar table with a date and IsWorking = 0 or 1 

Then it is easy to sum or slice the working days in a month. 

Please click thumbs up and accept as solution buttons. Thank you !

☹️

😀

[speedramps]

Hi agaian Anony51

Use a Calendar table with date, month and IsWorkingDay column (1 for working and null for non...

This is easy to create in the Power Query. See the above video link and do all the training modules.

Then you can slice or sum IsWorkingDay by week, month, quarter, year or between 2 dates.

Dont attempt to develop DAX measures for date logic. It is best practice to use Calendar.

Create and test a Calendar table once and use always. 😀

☠️

[speedramps]

It is best practice to use Calendar table for all Power BI date logic.

Develop and test once and then use always for MTD, YTD, TM, TY, PM, PY, slicing, grouping, drawing graphs and comparing this period with previous periods, etc, etc, etc

Don't attempt to create your own DAX date logic.

Click here to begin free Calendar table training 

Please click thumbs up and accept as solution buttons. Thank you !

[Anonymous]

Hi,

My date table is my calendar table in that I have all of my date data there and my working day measures are calculated from that. I have a relationship via the date column from the sales to date table.

Would you happen to know how to create measure that will allow filtering for working days of the selected month. 

Any point in the right direction would be helpful.

Thanks