Forum Discussion
Delta calculation based on dynamic Slicer
Hi,
I'm stuck with this New Measure field and I'd like to seek your help. We're taking Salesforce snapshot data and want to show comparison of forecast based on different snapshot date. Which snapshot date to pick will be on a slicer.
My sample date per below:
| Country | Forecast | Snapshot Date |
| A | 1,000 | Day 1 |
| B | 2,000 | Day 1 |
| C | 1,000 | Day 1 |
| D | 2,000 | Day 1 |
| E | - | Day 1 |
| A | 2,000 | Day 2 |
| B | 5,000 | Day 2 |
| C | - | Day 2 |
| D | 2,000 | Day 2 |
| E | 3,000 | Day 2 |
| A | 1,500 | Day 3 |
| B | 5,500 | Day 3 |
| C | - | Day 3 |
| D | 5,000 | Day 3 |
| E | 1,000 | Day 3 |
If user choose snapshot Day 1 and Day 2 in the Slicer, the result would be
| Snaphsot Date | Day 1 | Day 2 | Forecast Delta |
| A | 1,000 | 2,000 | 1,000 |
| B | 2,000 | 5,000 | 3,000 |
| C | 1,000 | - | (1,000) |
| D | 2,000 | 2,000 | - |
| E | - | 3,000 | 3,000 |
| 6,000 | 12,000 | 6,000 |
If user choose snapshot Day 2 and Day 3 in the Slicer, the result would be
| Snaphsot Date | Day 2 | Day 3 | Forecast Delta |
| A | 2,000 | 1,500 | (500) |
| B | 5,000 | 5,500 | 500 |
| C | - | - | - |
| D | 2,000 | 5,000 | 3,000 |
| E | 3,000 | 1,000 | (2,000) |
| 12,000 | 13,000 | 1,000 |
Would you be able to advise on this? Thanks a lot!
Hi ilui ,
Based on your description, you can create this measure:
Forecast Diff = VAR _max = CALCULATE ( MAX ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) ) VAR _min = CALCULATE ( MIN ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) ) VAR _maxsum = CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _max ) VAR _minsum = CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _min ) RETURN _maxsum - _minsumTo achieve the same visual, create two matrix to use visual overlay:
Attached the sample file that hopes to help you: Forecast data.pbix
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi ilui ,
Based on your description, you can create this measure:
Forecast Diff = VAR _max = CALCULATE ( MAX ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) ) VAR _min = CALCULATE ( MIN ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) ) VAR _maxsum = CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _max ) VAR _minsum = CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _min ) RETURN _maxsum - _minsumTo achieve the same visual, create two matrix to use visual overlay:
Attached the sample file that hopes to help you: Forecast data.pbix
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
v-yingjl Thanks heaps! The formula works well but for some reason, the Forecast Diff show up Twice. Look like below
I thought it's because of all the drilldown I have, but after removing the drilldown, it looks the same.
2020017 20200810 Country Forecast Forecast Diff Forecast Forecast Diff A $18,789,996 $4,807,466 $23,597,463 $4,807,466 B $12,156,438 $2,350,168 $14,506,605 $2,350,168 C $19,781,996 $3,535,801 $23,317,797 $3,535,801 $50,728,430 $10,693,435 $61,421,865 $10,693,435 I downloaded your sample but I cannot open it. Error message says "Object reference not set to an instance of an object"
Hi ilui ,
This is because measure cannot be used as a separate column in the matrix, it is by design in it.
As I previous posted, you can create two matrix and superimpose them, set the date matrix arrange as 'Bring to front' in the Format pane, adjust the size of two matrix visuals properly.
I have re-uploaded the sample file that you can refer it, please try to open it with the latest version of power bi desktop (August 2020) because it is currently known that there is a problem that the lower version cannot be used to open the higher version file.
Sample file: Forecast.pbix
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
ilui , You can use day vs last day and take a diff. Use date table.
Example
Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date]))) Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day)) Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf]) ),Table[Date]) ,Table[Date],Day) This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date]))) Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1)) Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.The snapshot date is not necessarily Day -1. The snapshot data is twice a week so the date can be
20200511
20200516
20200518 etc etc
also, are below a number of Measures? or 1 measure?
