Last month working MTD vs This month working MTD. HELP

Anonymous — Thu, 02 Feb 2023 12:05:38 GMT

Good Morning

I am trying to compare a count of a column this month going off working days vs last month's working days.

I used the following community post to get the working day number next to the calendar dates so, for example, this month the 1st working day is the 1st and last month the 1st working day was the 2nd both have a 1 next to them.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Same-working-day-last-month/m-p/2714230

I can get the counts to show vs the date field but I want this to show against a department without the date fields on the page.

But instead, I would like it

I have joined the calendar table to my data table but I cannot figure out how to only to count the files opened between the first working day and the current working day.

Help would be appreciated

Re: Last month working MTD vs This month working MTD. HELP

Anonymous — Thu, 02 Feb 2023 13:23:30 GMT

Solved it, if you need this add the following to your calendar table:

WorkdaySort =
VAR _sort =
CALCULATE (
COUNT ( 'Calendar'[Date] ),
FILTER (
'Calendar',
'Calendar'[Month] = EARLIER ( 'Calendar'[Month] )
&& 'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] )
&& 'Calendar'[Year] = EARLIER( 'Calendar'[Year] )
&& NOT 'Calendar'[Day of Week] IN { 5, 6 }
)
)
VAR _result =
IF ( NOT 'Calendar'[Day of Week] IN { 5, 6 }, _sort )
RETURN
_result

Then the following as your measure:

Last Month MTD =
VAR _MINworkday_sort =
CALCULATE ( MIN( 'Calendar'[WorkdaySort] ), 'Calendar'[Date] = EOMONTH(TODAY(),0)+1 )
VAR _MAXworkday_sort =
CALCULATE ( MAX( 'Calendar'[WorkdaySort] ), 'Calendar'[Date] = TODAY() )
return
CALCULATE (
COUNT(Matters[cMatter]),
FILTER (
ALL('Calendar'),
'Calendar'[Year] = YEAR(TODAY())
&& 'Calendar'[Month] = MONTH(TODAY())-1
&& 'Calendar'[WorkdaySort] >= _MINworkday_sort
&& 'Calendar'[WorkdaySort] <= _MAXworkday_sort
)
)

Change the count to your measure or sum or count and it will pull in your last month-to-date working days aggregation.

topic Last month working MTD vs This month working MTD. HELP in DAX Commands and Tips

Last month working MTD vs This month working MTD. HELP

Re: Last month working MTD vs This month working MTD. HELP