topic Re: DAX Comparing counts from two different snapshots in DAX Commands and Tips

DAX Comparing counts from two different snapshots

Anonymous — Wed, 23 Feb 2022 11:51:26 GMT

Hi all,

This should be quite straightforward, but I can't seem to be able to debug it yet...

I have a series of data called 'Opportunities' that are recorded in an excel spreadsheet.

Opportunities

Opportunity 1

Opportunity 2

Opportunity 3

Opportunity 4 ...

Every week, we capture a snapshot of the list and assign a date in the 'Date Captured' column.

Date Captured	Opportunities	Projects
10/02/2022	Opportunity 1	Opportunity
10/02/2022	Opportunity 2	Opportunity
10/02/2022	Opportunity 3	Opportunity
17/02/2022	Opportunity 1	Opportunity
17/02/2022	Opportunity 2	Opportunity
17/02/2022	Opportunity 3	Opportunity
17/02/2022	Opportunity 4	Opportunity
24/02/2022	Opportunity 1	Opportunity
24/02/2022	Opportunity 2	Opportunity
24/02/2022	Opportunity 3	Opportunity
24/02/2022	Opportunity 4	Opportunity
24/02/2022	Opportunity 5	Opportunity

What I would like to have is a summary table with the latest number of opportunities (5), a dynamic table that shows the difference between the current/latest number of Opportunities and a previous date (selected using a slicer of the 'Date Captured'), and a table that shows the number of Opps for that previous date (3).

Latest Date	Count
24/02/2022	5

Difference

Earlier Date	Count
10/02/2022	3

I have written the following codes, but I keep getting 0s in the middle table (difference). Any help?

Step 1:

Opportunties_Count = CALCULATE (COUNT('Table'[Projects]),

FILTER('Table',[Projects]="Opportunity"))

Step 2:

LatestDateCount =

VAR LatestDate= MAX ('Table'[Date Captured] )

VAR LatestDateCount= CALCULATE ( [Opportunities_Count],'Table'[Date Captured] = LatestDate)

RETURN CALCULATE(LatestDateCount)

Step 3:

EarlierDateCount =

VAR EarlierDate= SELECTEDVALUE('Table'[Date Captured] )

VAR EarlierDateCount= CALCULATE ( [Opportunities_Count],'Table'[Date Captured] = EarlierDate)

RETURN CALCULATE(EarlierDateCount)

Step 4:

Difference = LatestDateCount - EarlierDateCount

Please note that I do get the right values in the top and bottom tables. The only one that doesn't seem to work is the Difference table.

Re: DAX Comparing counts from two different snapshots

amitchandak — Wed, 23 Feb 2022 03:19:05 GMT

Anonymous , Try like

Opportunties_Count = CALCULATE (COUNT('Table'[Projects]),
FILTER('Table',[Projects]="Opportunity"))

Step 3:
LatestDateCount =
VAR LatestDate= MAXX (allseleceted('Table'), 'Table'[Date Captured] ) //or max('Table'[Date Captured])
VAR LatestDateCount= CALCULATE ( [Opportunities_Count],filter('Table', 'Table'[Date Captured] = LatestDate) )
RETURN CALCULATE(LatestDateCount)

Step 3:
EarlierDateCount =
VAR LatestDate= MAXX (allseleceted('Table'), 'Table'[Date Captured] )
VAR EarlierDate= MAXX (filter(allseleceted('Table'), 'Table'[Date Captured] < LatestDate), 'Table'[Date Captured] )
VAR EarlierDateCount= CALCULATE ( [Opportunities_Count],filter('Table', 'Table'[Date Captured] = EarlierDate) )
RETURN CALCULATE(EarlierDateCount)

Step 4:
Difference = LatestDateCount - EarlierDateCount

In case you want select a date and compare. better to have separate table date table, because you need all for EarlierDateCount

EarlierDateCount =
VAR LatestDate= MAXX (allseleceted('Date'), 'Date'[Date] )
VAR EarlierDate= calculate(MAX('Table'[Date Captured] ),filter(all('Date'), 'Date'[Date] < LatestDate))
VAR EarlierDateCount= CALCULATE ( [Opportunities_Count],filter(all('Date'), 'Date'[Date] = EarlierDate) )
RETURN CALCULATE(EarlierDateCount)

Re: DAX Comparing counts from two different snapshots

Anonymous — Wed, 23 Feb 2022 11:48:13 GMT

Many thanks amitchandak

Your suggestion seems to work in principle, but doesn't seem to work when I use a slicer for the past date.

In such case, the middle comparison table gets the previous date values, and the bottom table (previous date values) becomes blank.

Not sure what's the problem...

Re: DAX Comparing counts from two different snapshots

ERD — Wed, 23 Feb 2022 12:29:29 GMT

Hi Anonymous ,

I'm not sure what you want to achieve:

Do you want to compare the values between the LATEST day available in a table and a day you choose in a slicer?

If so:

LatestDateCount = VAR LatestDate = MAXX ( ALL ( 'Table' ), 'Table'[Date Captured] ) VAR LatestDateCount = CALCULATE ( [Opportunties_Count], 'Table'[Date Captured] = LatestDate ) RETURN LatestDateCount

EarlierDateCount = VAR EarlierDate = SELECTEDVALUE ( 'Table'[Date Captured] ) VAR EarlierDateCount = CALCULATE ( [Opportunties_Count], 'Table'[Date Captured] = EarlierDate ) RETURN EarlierDateCount

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: DAX Comparing counts from two different snapshots

Anonymous — Wed, 23 Feb 2022 12:50:27 GMT

Thanks ERD! That works exactly as I wanted. Seems like the main difference between my code and yours was the MAX vs MAXX. Good lesson learned!