Help with DAX function to count items per day, for the prior day's reporting period (exc weekends)

[RobAdair]

Hello, 

I have a report where i am pulling a daily file of items into tables through powerquery, and then i want to build a dashboard showing the day on day change of counts of various fields (e.g. all items which are on the table per day, all items over 10 days old, etc). I then will use this data (including the measures) to build out a matrix, with Name as the rows, Report Date as columns, and the counts and measures in the values.

My downfall has been calculating the day on day difference. I am using a measure to count the items per day, and then i thought I'd found a really good looking piece of DAX for a further measure to store the prior business days count, where i then use a final measure to count the difference between the two. Unfortunately, the count of the prior business days seems to be randomly out on some days - and i am sure i have done something wrong! If you can advise that would be excellent!

For example

Table

Report Date	Item ID	Name	Index
08-Nov-22	123456	Rob	9
08-Nov-22	654321	Steve	8
07-Nov-22	578132	Rob	7
07-Nov-22	685711	Andy	6
07-Nov-22	166664	Andy	5
06-Nov-22	684213	Steve	4
06-Nov-22	156651	Andy	3
06-Nov-22	354610	Rob	2
06-Nov-22	566451	Brian	1

Measure 1

CountPerDay = Calculate(Count('Table'[Item ID]))

Measure 2

Count Day-1 =

VAR __CurrentDate =

MAX ('Table'[Report Date])

VAR _LastDate =

LASTDATE(

FILTER(

ALL('Table'[Report Date]),

'Table'[Report Date] < __CurrentDate

&& NOT WEEKDAY('Table'[Report Date],2) in {6, 7}

)

)

VAR __Result =

Calculate(Count('Table'[Item ID]), _LastDate)

Return

__Result

Measure 3

Difference = [CountPerDay] - [Count Day-1]

Expected Result

	06-Nov-22	07-Nov-22	08-Nov-22
	Count	Difference	Count	Difference	Count	Difference
Rob	1		1	0	1	0
Steve	1		0	-1	1	1
Andy	1		2	1	0	-2
Brian	1		0	-1	0	0

[v-easonf-msft · Accepted solution]

Hi， @RobAdair 

It is recommended to create a separate calendar table and perform calculations based on this calendar table.

Count Day-1 = 
VAR _CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR _LastDate =
    LASTDATE (
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] < _CurrentDate
                && NOT WEEKDAY ( 'Calendar'[Date], 2 ) IN { 6, 7 }
        )
    )
VAR _Result =
    CALCULATE ( COUNT ( 'Table'[Item ID] ), _LastDate )
RETURN
    _Result + 0

You may also need to check the expected result. 

07-Nov-22 is Monday, the value of difference for 07-Nov-22 should be blank as well.

Best Regards,
Community Support Team _ Eason

[AnnieLou]

I have a similar issue trying to do time intelligence day changes in ticket volumes - would be great if you can let me know if you get a solution