topic Re: Current Month Run Rate with only business days in DAX Commands and Tips

Current Month Run Rate with only business days

Anonymous — Mon, 12 Dec 2022 18:02:00 GMT

I am trying to figure out a way to calculate a run rate based on only business days in a fiscal month. For example our fiscal mnth runs from the 22nd through the 21st. So as an example Fiscal December would exclude weekends plus Thanksgiving and only count business days between November 22 and December 21. The goal would basicaly be to build an automated run rate of units by taking the average daily volume on business days lapsed and extrapoliting to the end of the month.

I have this calc for days remanining in a fiscal month but its not account for business days. Addtionally i'm struggling to get figure out how to dynamically get the number of days passed in the fiscal month

_DAYS_REMAINING_MTD =

VAR ref_dte = TODAY () - 2

VAR fiscal_mo_end_dte = DATE ( IF ( DAY ( ref_dte ) > 21, YEAR ( EDATE ( ref_dte, 1 ) ), YEAR ( ref_dte ) ), IF ( DAY ( ref_dte ) > 21, MONTH ( EDATE ( ref_dte, 1 ) ), MONTH ( ref_dte ) ), 21 )

VAR days_remaining = DATEDIFF ( ref_dte, fiscal_mo_end_dte, DAY )

RETURN

days_remaining

Re: Current Month Run Rate with only business days

tamerj1 — Mon, 12 Dec 2022 18:24:27 GMT

Hi Anonymous

if you have Holidays table that contains all weekends and holidays then

VAR days_remaining =
COUNTROWS (
EXCEPT ( CALENDAR ( ref_dte, fiscal_mo_end_dte ), VALUES ( Holidays[Holiday] ) )
)
RETURN
days_remaining

Re: Current Month Run Rate with only business days

Manoj_Nair — Mon, 12 Dec 2022 19:16:32 GMT

Anonymous - try use NETWORKDAYS Syntax. Let me know if this works. Thanks

Re: Current Month Run Rate with only business days

Anonymous — Mon, 12 Dec 2022 19:35:49 GMT

Thanks...how would that bake into my original Dax formula? Basically the idea would be to automatically roll it over month to month...

Re: Current Month Run Rate with only business days

Anonymous — Mon, 12 Dec 2022 19:40:04 GMT

Also did you create a table with holidays and weekends?

Re: Current Month Run Rate with only business days

Manoj_Nair — Mon, 12 Dec 2022 19:45:40 GMT

Anonymous yes we created a holiday list, for weekend we used 1 assuming your week ends are Sat and Sun. You can change this depends on your weekends in your company. All details are in Microsoft documentation. Many Thanks

Re: Current Month Run Rate with only business days

Anonymous — Tue, 13 Dec 2022 03:25:35 GMT

Hi Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

DAYS_REMAINING_MTD = var _today=TODAY() var _ThanksgivingDate= SELECTCOLUMNS( FILTER(ALL('Table'),'Table'[Date]>=DATE(2022,11,24)&&'Table'[Date]<=DATE(2022,11,27)),"Date",[Date]) return COUNTX( FILTER(ALL('Table'), AND('Table'[Date] >= DATE(YEAR(_today),11,22) ,'Table'[Date]<=DATE(YEAR(_today),12,21)) && NOT( WEEKDAY('Table'[Date],2) ) in {6,7} &&NOT('Table'[Date]) in _ThanksgivingDate ),[Date])

2. Result:

Best Regards,

Liu Yang

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

Re: Current Month Run Rate with only business days

Anonymous — Tue, 13 Dec 2022 14:35:49 GMT

Thanks, very helpful! The part I'm not getting is the days passed in a month as opposed to the days remaining...so for example on December 5 I would want to know how many business days passed between the 22nd and 5th and then how many remain which you already covered. I've tried NETWORKDAYS but couldn't get that to work

Re: Current Month Run Rate with only business days

Manoj_Nair — Tue, 13 Dec 2022 15:36:02 GMT

Anonymous What I shared was about a function "Networkdays" which gives you working days between 2 dates. So you have to do it in 2 parts if you pass the dates which have lapsed eg 22-Nov-2022 and Today() you get working days that have passed and if you pass Today() and 21-Dec-2022 you get working days that are remaining. Hope I understood your problem correctly

Re: Current Month Run Rate with only business days

Anonymous — Tue, 13 Dec 2022 15:42:01 GMT

Thanks...what I meant is I'm getting an error that NETWORKDAYS is not valid function

Re: Current Month Run Rate with only business days

Manoj_Nair — Tue, 13 Dec 2022 16:14:05 GMT

Anonymous Ohh, you have to update your Power BI desktop version to latest (I think July 2022 onwards this became available)