Comparing MTD to Previous MTD based on business days

[Anonymous]

I having some trouble. 

I am trying to compare the MTD Volume to the Previous MTD Volume but based on business days.  For example the MTD up to the 20th business day in May (5/28/2019) to the MTD of the previous business month in April (4/26/2019)

I have created a Date table where my week is from Monday to Sunday (1 -7).

Thank you for any assistance!

[Anonymous]

Here is an example of what I hope to get as a result.

@parry2k @jdbuchanan71 

[jdbuchanan71 · Accepted solution]

Hello @Anonymous ,

For this solution we will need to add a couple columns to your date table.  One for the YearMonth number, one to check if it is a Sat or Sunday, and one to calc the working day of the month.  These are added as calculated columns to your date table.

YearMonthSort = YEAR( [Date] )*100 + MONTH([Date])

IsWorkingDay = NOT WEEKDAY( [Date] ) IN {1,7}

Working Day of Month = 
VAR CurrentMonth = Dates[Year Month]
VAR CurrentRow = Dates[Date]
RETURN
CALCULATE ( 
    COUNTROWS( Dates ),
    ALL ( Dates ),
    Dates[Year Month] = CurrentMonth,
    Dates[Date] < CurrentRow,
    Dates[IsWorkingDay] = TRUE
) +1 

You will need a measure to just sum the volume

Volume Amount = SUM ( 'Table'[Volume] )

Then we can add the Prior Month MTD filtered measure

PMMTD_Filtered = 
VAR MaxWorkingDay = MAX ( Dates[Working Day of Month] )
VAR MonthYear = SELECTEDVALUE ( Dates[YearMonthSort] )
RETURN
CALCULATE(
    [Volume Amount],
    ALL ( Dates ),
    Dates[YearMonthSort] = MonthYear - 1,
    Dates[Working Day of Month] <= MaxWorkingDay
)

You can see in my example, the straight PM_MTD_Raw returns the amount that is through the 14th (the wrong amount) but the PMMTD_Filtered returns only the Volume through Day 10.

I uploaded my sample file for you to look at.  https://www.dropbox.com/s/lm1je1c9uxjcrdy/WorkingDays.pbix?dl=0

[Anonymous]

Thank you, this is perfect! @jdbuchanan71 

I have a field that calculates if it is a weekend or a holiday, so I will see if I can use that for the ISWORKDAY field, but I wanted to ask how do i make it where the Saturday, Sunday, and/or Holiday calculates to the last business day instead of the next day i.e. 

Day

Sat = 5

Sun = 5

Mon = 6

[jdbuchanan71]

So you want the Sat and Sun counted as the Friday working day of month, not the Monday.

Just have to adjust the [Working Day of Month] coulmn calculation a bit.

Working Day of Month = 
VAR CurrentMonth = Dates[Year Month]
VAR CurrentRow = Dates[Date]
RETURN
CALCULATE ( 
    COUNTROWS( Dates ),
    ALL ( Dates ),
    Dates[Year Month] = CurrentMonth,
    Dates[Date] <= CurrentRow,
    Dates[IsWorkingDay] = TRUE
)