Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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])
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.
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.
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])
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.
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?
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |