I wasn't sure exactly how to title this question.
I have a table that has Four columns, Type, Version, Month and quantity. In January, all twelve months are loaded and each month type is Forecast, Version is V1. Then in February, I add twelve more rows, January is Actuals, February through December is Forecast, Version is V2. Then in March, I add twelve more rows, January, February type is Actuals and March through December is Forecast, Version is V3.
I have a slicer for Version. When the user selects V3, I need it to sum January, February, Actuals and March through December Forecast. If I create the table as I have it shown above then it is pretty straight forward, but my data model is large (there are 300k rows for each month).
What I would like to accomplish is: I load the table with the V1, 12 months forecast, then in February I load January Actuals, and February through December Forecast in V2 (because the forecast amounts change each time a version is created). Then in March, I load February actuals, March through Decembert forecast into V3.
When the user selects V2, the formula should grab January Actuals, February through December V2 forecast. If the user selects V3, the formula grabs January and February actuals, March through December V3 Forecast.
Thoughts on the best way to accomplish this? I thought of a switch formula (Metric Selected) or maybe a index table that has all the various scenarios listed. But wanted to see the thoughts of others.
Well the type matters because based on the type, i have calculations that use this field to determine how to calculate. But it does not matter in the visual matrix, I only included it to make it easier for people reading this to see what I was trying to explain.
I got it working but I didn't know if there was an easier way to accomplish it.
Yes, I have a year column, there are currently three years in the model. Each sales order item has individual products, all with various characteristics. I use these characteristics for different visuals and the users like to see multiple years at one time.
I try to use excel to reduce the data set to a more manageable data set but it still ends up being a lot of data.
Could you do something where you Calculate the Actuals and add in the selected forecast?
measure = CALCULATE(SUM(Quantity),Type="Actuals")+CALCULATE(SUM(Quantity),Type="Forecast",Version=SELECTEDVALUE(Version))
You may want to break up those into individual measures or use variables but the idea is there.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.