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])

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 result:

Measure = 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(ALLEXCEPT('Table','Table'[Deal ID]),[SnapshotDate]=c))
var closedateofbeginningsnapshotdate = CALCULATE(MAX('Table'[CloseDate]),FILTER(ALLEXCEPT('Table','Table'[Deal ID]),[SnapshotDate]=d))
Return
CALCULATE(SUM('Table'[Amount]),FILTER('Table',closedateofendsnapshotdate>closedateofbeginningsnapshotdate&&closedateofendsnapshotdate>a&&'Table'[CloseDate]=closedateofendsnapshotdate))

Pbix attached.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    thank you, can you please let me know what Table 2 and Table 3 are?

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

      HI Anonymous 

       

      They're calendar tables for bringing the slicer of the snapshot date selected and close date selected, coz we are not able to use the original table's column as a slicer to filter the expected result.

       

      • 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?