Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
85 | |
84 | |
67 | |
63 | |
62 |
User | Count |
---|---|
208 | |
121 | |
112 | |
79 | |
71 |