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 changes.

 

Users will select the following:

1. A date range using snapshot date
2. A date range using close date

 

We need a measure that does the following:

Get snapshot start date & snapshot end date from the user selection, compare the deals as of the "start date" vs the "end date".  For a given deal, if the close date of a deal as of "end snapshot date" is greater than the close date of the same deal as of "beginning snapshot date" AND the close date of the deal as of "end snapshot date" is outside of the close date range selected by the user, then grab the amount from the deal as of the "end snapshot date".

 

Is this possible?

 

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

8 Replies

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

    Hi Anonymous 

     

    Not quite understand your logic, could you please clarify more about this sentence? 

    For a given deal, if the close date of a deal as of "end snapshot date" is greater than the close date of the same deal as of "beginning snapshot date" AND the close date of the deal as of "end snapshot date" is outside of the close date range selected by the user,

     

    what does the "end snapshot date" means?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      v-diye-msft wrote:

       

      what does the "end snapshot date" means?

       

      Users will select a data range using the snapshot date field. For example, a user might select 1/1/2019 to 1/31/2019 in this case the end snapshot is 1/31/2019

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