Is this possible using DAX

[Anonymous]

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?

[v-diye-msft]

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]

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

[v-diye-msft]

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]

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 · Accepted solution]

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]

Hi @Anonymous 

Could you please kindly mark my answer as a solution for the original question? That will help others find it more quickly. thanks a lot!

I'll draw it up for your new question at my earlier convenience.

[v-diye-msft]

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]

---

@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