Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two tables. The first one is Actual sales.
Date | Client | Scenario | Metric | Data |
5/1/2017 | A | Actual | Gross Sales | 500 |
5/2/2017 | A | Actual | Gross Sales | 500 |
5/3/2017 | A | Actual | Gross Sales | 500 |
5/4/2017 | A | Actual | Gross Sales | 500 |
5/5/2017 | A | Actual | Gross Sales | 500 |
The second is a forecast table
Date | Client | Scenario | Metric | Data |
5/1/2017 | A | Planned | Gross Sales | 1000 |
5/2/2017 | A | Planned | Gross Sales | 1000 |
5/3/2017 | A | Planned | Gross Sales | 1000 |
5/4/2017 | A | Planned | Gross Sales | 1000 |
5/5/2017 | A | Planned | Gross Sales | 1000 |
5/1/2017 | A | Current Forecast | Gross Sales | 900 |
5/2/2017 | A | Current Forecast | Gross Sales | 900 |
5/3/2017 | A | Current Forecast | Gross Sales | 900 |
5/4/2017 | A | Current Forecast | Gross Sales | 900 |
5/5/2017 | A | Current Forecast | Gross Sales | 900 |
In the second table there are two types of forecasts. One is Planned and the other is Current Forecast. I have measures created for all three. However I want to be able to have a slicer on my reports that lets me change my calcuations based on Planned and Current Forecast. For instance I want my MTD variance to be [Total Actual] - [Total Planned or Current Forecast} based on which one I have selected.
Any ideas on the best way to do this?
Thank you,
Joseph
Hi Joseph,
First, we need a Date table and then establish relationships with ActualSales and ForcastSales. This can ensure we evaluate the MTD in the same time period. We can create a Date table with this formula.
Date = CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) )
Actually, we just need one measure. But we create three to see the steps.
TotalActual = TOTALMTD ( SUM ( ActualSales[Data] ), 'Date'[Date] )
TotalForcast = TOTALMTD ( SUM ( ForecastSales[Data] ), 'Date'[Date] )
Result = TOTALMTD ( SUM ( ActualSales[Data] ), 'Date'[Date] ) - TOTALMTD ( SUM ( ForecastSales[Data] ), 'Date'[Date] )
or
Result = [TotalActual] - [TotalForcast]
Finally we can create a slicer with ForcastSales[scenario]. And a slicer with 'Date'[date] if needed.
Best Regards!
Dale
User | Count |
---|---|
107 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |