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 

 

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