topic Week Over Week DAX Count for Previous Week in DAX Commands and Tips

Week Over Week DAX Count for Previous Week

ULTRAVI0LENCE — Wed, 15 Nov 2023 21:22:55 GMT

I've created a measure that uses calculated columns that counts rows with a specific value, see below.

Calls This Week = CALCULATE(COUNTROWS(AllContacts),FILTER(AllContacts,AllContacts[Contact_System]="Call Center"),FILTER(ALL(AllContacts),AllContacts[WeekRank]=MAX(AllContacts[WeekRank])))

This expression works & gives me an accurate number of calls for a given week with the filter applied for only rows with "Call Center". The next step would be to calculate the same data for the previous week. The DAX I wrote for that purpose is very similar below, but does not return anything.

Calls Last Week = CALCULATE(COUNTROWS(AllContacts),FILTER(AllContacts,AllContacts[Contact_System]="Call Center"),FILTER(ALL(AllContacts),AllContacts[WeekRank]=MAX(AllContacts[WeekRank])-1))

I subtract 1 from the week rank to return the previous week, but this does not prove to be useful. Anyone familiar with this issue?

Re: Week Over Week DAX Count for Previous Week

FreemanZ — Thu, 16 Nov 2023 01:55:17 GMT

Hi ULTRAVI0LENCE ，

try like:

CountPreviousWeekColumn=

COUNTROWS(

FILTER(

AllContacts,

AllContacts[Contact_System]="Call Center"

&&AllContacts[WeekRank] = EARLIER(AllContacts[WeekRank]) -1

)

Re: Week Over Week DAX Count for Previous Week

ULTRAVI0LENCE — Thu, 16 Nov 2023 16:49:15 GMT

Thank you for the reply,

The EARLIER DAX expression does not accept my column AllContacts[WeekRank] as a parameter. An error is thrown on this specific parameter. Any tips on this?