cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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?

 

Untitled.png

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

009.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

8 REPLIES 8
Community Support
Community Support

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

02.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

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

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

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?

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

009.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Community Support
Community Support

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?

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable


@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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors