Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Comparing counts from two different snapshots

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.   Opp...
comparison
DAX
snapshot
  • ERD's avatar
    ERD
    4 years ago

    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.

amitchandak's avatar
amitchandak
Super User
4 years ago

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)