Current Month Run Rate with only business days

[Anonymous]

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

[v-yangliu-msft]

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

[Anonymous]

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

[Manoj_Nair]

@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

[Anonymous]

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

[Manoj_Nair]

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

[Manoj_Nair]

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

 

[Anonymous]

Also did you create a table with holidays and weekends?

[Manoj_Nair]

@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

[Anonymous]

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

[tamerj1]

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