cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Comparing MTD to Previous MTD based on business days

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!

1 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.

PMMTDWorkingDays.jpg

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

 

 

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

 

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

@parry2k @jdbuchanan71 

 

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.

PMMTDWorkingDays.jpg

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

 

 

 

Anonymous
Not applicable

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

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
)

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors