Forum Discussion
Measure scenario...is it possible?
Hello,
At the Business Applications Summit back in July it was stressed that one should try to always use measures instead of calculated columns, so as not to bog down the system. I have a scenario that I've been unable to create a measure for and I'm curious if it's even possible.
Here is the scenario:
We have a forecast each month that extends from the current month till the end of the year. So the 2018 October Forecast is from Oct to Dec. We want to compare forecast versions, but it's not apples-to-apples since the October Forecast is 3 months whereas the January Forecast would be for 12 months. Therefore, I need to create a scenario in which each forecast month includes actuals from the prior months. So the January amount (12 months of forecast) could be compared against the October amount (3 months of forecast and 9 prior months of actuals).
I have already solved this using calculated columns in a matrix, but I needed to create a separate column for each month. 12 calculated columns. I would love to streamline this into a single measure.
Here is the my DAX function for the October Amount calculated column...
October Amount=
IF ( OR ('DATA_TABLE'[FORECAST_VERSION] = "10 Oct",
AND ('DATA_TABLE'[TRANSACTION_PERIOD] < 10,'DATA_TABLE'[FORECAST_VERSION] = "ACTUAL")),
'DATA_TABLE'[SALES_AMOUNT],
0
)
Any help would be appreciated. Thanks!
8 Replies
- Ashish_Mathur
Super User
Hi,
Share some data and show the expected result.
- v-yuta-msft
Community Support
Hi Trudgeon,
"We have a forecast each month that extends from the current month till the end of the year. So the 2018 October Forecast is from Oct to Dec. We want to compare forecast versions, but it's not apples-to-apples since the October Forecast is 3 months whereas the January Forecast would be for 12 months. Therefore, I need to create a scenario in which each forecast month includes actuals from the prior months. So the January amount (12 months of forecast) could be compared against the October amount (3 months of forecast and 9 prior months of actuals)."
<--- Your description of your logic is not so clear, could you share more details about your requirement?
Regards,
Jimmy Tao
- Trudgeon
Helper III
Here is some exmaple data, along with expected results...
Raw Data:
VERSION PRODUCT GROUP PERIOD YEAR AMOUNT 01 JAN FORECAST Cogs 1 2018 450 01 JAN FORECAST Cogs 2 2018 350 01 JAN FORECAST Cogs 3 2018 400 01 JAN FORECAST Cogs 4 2018 600 01 JAN FORECAST Cogs 5 2018 500 01 JAN FORECAST Cogs 6 2018 600 01 JAN FORECAST Cogs 7 2018 1000 01 JAN FORECAST Cogs 8 2018 2000 01 JAN FORECAST Cogs 9 2018 850 01 JAN FORECAST Cogs 10 2018 750 01 JAN FORECAST Cogs 11 2018 800 01 JAN FORECAST Cogs 12 2018 850 01 JAN FORECAST Widgets 1 2018 5000 01 JAN FORECAST Widgets 2 2018 5100 01 JAN FORECAST Widgets 3 2018 3000 01 JAN FORECAST Widgets 4 2018 5300 01 JAN FORECAST Widgets 5 2018 2000 01 JAN FORECAST Widgets 6 2018 5500 01 JAN FORECAST Widgets 7 2018 5600 01 JAN FORECAST Widgets 8 2018 5700 01 JAN FORECAST Widgets 9 2018 5800 01 JAN FORECAST Widgets 10 2018 5900 01 JAN FORECAST Widgets 11 2018 6000 01 JAN FORECAST Widgets 12 2018 6100 02 FEB FORECAST Cogs 2 2018 275 02 FEB FORECAST Cogs 3 2018 275 02 FEB FORECAST Cogs 4 2018 400 02 FEB FORECAST Cogs 5 2018 400 02 FEB FORECAST Cogs 6 2018 400 02 FEB FORECAST Cogs 7 2018 600 02 FEB FORECAST Cogs 8 2018 600 02 FEB FORECAST Cogs 9 2018 600 02 FEB FORECAST Cogs 10 2018 800 02 FEB FORECAST Cogs 11 2018 800 02 FEB FORECAST Cogs 12 2018 800 02 FEB FORECAST Widgets 2 2018 4925 02 FEB FORECAST Widgets 3 2018 5075 02 FEB FORECAST Widgets 4 2018 5225 02 FEB FORECAST Widgets 5 2018 5375 02 FEB FORECAST Widgets 6 2018 5525 02 FEB FORECAST Widgets 7 2018 5675 02 FEB FORECAST Widgets 8 2018 5825 02 FEB FORECAST Widgets 9 2018 5975 02 FEB FORECAST Widgets 10 2018 6125 02 FEB FORECAST Widgets 11 2018 6275 02 FEB FORECAST Widgets 12 2018 6425 03 MAR FORECAST Cogs 3 2018 900 03 MAR FORECAST Cogs 4 2018 900 03 MAR FORECAST Cogs 5 2018 900 03 MAR FORECAST Cogs 6 2018 900 03 MAR FORECAST Cogs 7 2018 900 03 MAR FORECAST Cogs 8 2018 900 03 MAR FORECAST Cogs 9 2018 900 03 MAR FORECAST Cogs 10 2018 900 03 MAR FORECAST Cogs 11 2018 900 03 MAR FORECAST Cogs 12 2018 1500 03 MAR FORECAST Widgets 3 2018 5500 03 MAR FORECAST Widgets 4 2018 5500 03 MAR FORECAST Widgets 5 2018 5500 03 MAR FORECAST Widgets 6 2018 5500 03 MAR FORECAST Widgets 7 2018 5500 03 MAR FORECAST Widgets 8 2018 5500 03 MAR FORECAST Widgets 9 2018 5500 03 MAR FORECAST Widgets 10 2018 5500 03 MAR FORECAST Widgets 11 2018 5500 03 MAR FORECAST Widgets 12 2018 5500 ACTUAL Widgets 1 2018 1000 ACTUAL Widgets 1 2018 200 ACTUAL Widgets 1 2018 1000 ACTUAL Widgets 1 2018 450 ACTUAL Widgets 1 2018 2000 ACTUAL Widgets 1 2018 300 ACTUAL Cogs 1 2018 45 ACTUAL Cogs 1 2018 450 ACTUAL Cogs 1 2018 35 ACTUAL Widgets 2 2018 3500 ACTUAL Widgets 2 2018 1000 ACTUAL Widgets 2 2018 450 ACTUAL Cogs 2 2018 100 ACTUAL Cogs 2 2018 150 ACTUAL Cogs 2 2018 75 ACTUAL Widgets 3 2018 3500 ACTUAL Cogs 3 2018 50 ACTUAL Cogs 3 2018 60 - Ashish_Mathur
Super User
Hi,
The figure shown in your expected result image of your previous post does not tally with the data you have pasted. Actual for widgets for Jan ans Feb is 13,400. Forecast for widgets in March for the balance 10 months is 55,000. The total should be 68,400. Your result is 64,450. How does one explain that difference?
- Trudgeon
Helper III
According to the numbers I'm seeing, actual for widgets for Jan & Feb is 9,900 and actual for widgets for Jan & Feb & Mar is 13,400. Therefore, my expected result is 64,450...March for the balance 10 months plus 9,900. None of the March actuals would be included in the expected results. I should not have included them in the sample data since it only serves to make it more confusing.