Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Is this possible using DAX

Hi All - I have a monhtly snapshot table that captures our deal history as shown below in the screen shot. Goal is to compare two different months and see if the close date and/or amounts have change...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi Anonymous 

     

    Let me know if you'd like to get below results:

    Measure 2 = var a = MAX('Table 2'[CloseDate selected])
var b = MIN('Table 2'[CloseDate selected])
var c= MAX('Table 3'[SnapshotDate selected])
var d = MIN('Table 3'[SnapshotDate selected])
var closedateofendsnapshotdate = CALCULATE(MAX('Table'[CloseDate]),FILTER('Table',[SnapshotDate]=c))
var closedateofbeginningsnapshotdate = CALCULATE(MAX('Table'[CloseDate]),FILTER('Table',[SnapshotDate]=d))
Return CALCULATE(SUM('Table'[Amount]),FILTER('Table',closedateofendsnapshotdate>closedateofbeginningsnapshotdate&&closedateofendsnapshotdate>a&&'Table'[CloseDate]=closedateofendsnapshotdate))
    Measure 3 = SUMX('Table',[Measure 2])

Anonymous's avatar
Anonymous
Not applicable
6 years ago

The logic is working only when the Deal Id is used in the table. If I want to look at the aggregate value (without Deal Id), it shows blank. Any idea how to modify it to work without Deal ID?

v-diye-msft's avatar
v-diye-msft
Community Support
6 years ago

Hi Anonymous 

 

Let me know if you'd like to get below results:

Measure 2 = var a = MAX('Table 2'[CloseDate selected])
var b = MIN('Table 2'[CloseDate selected])
var c= MAX('Table 3'[SnapshotDate selected])
var d = MIN('Table 3'[SnapshotDate selected])
var closedateofendsnapshotdate = CALCULATE(MAX('Table'[CloseDate]),FILTER('Table',[SnapshotDate]=c))
var closedateofbeginningsnapshotdate = CALCULATE(MAX('Table'[CloseDate]),FILTER('Table',[SnapshotDate]=d))
Return CALCULATE(SUM('Table'[Amount]),FILTER('Table',closedateofendsnapshotdate>closedateofbeginningsnapshotdate&&closedateofendsnapshotdate>a&&'Table'[CloseDate]=closedateofendsnapshotdate))
Measure 3 = SUMX('Table',[Measure 2])