cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User

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

4 REPLIES 4
Anonymous
Not applicable

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

Super User

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

Super User

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
)```

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.