Forum Discussion
How to shift date axis using what-if parameter?
Anonymous
Here is my edited version of your PBIX.
link- First I created a parameter table DateOffset using Modelling => New Parameter.
- Added a slicer for this parameter as in your screenshot
- I also marked your DimDate table as a date table. This is best to do when you are using time intelligence functions.
- I created measures called Primary & Indicator, just for the sake of consistency with your earlier screenshot.
Indicator uses DATEADD to shift the date filter by the negative of the DateOffset value selected. This gives the appearance of shifting Indicator to the right if a positive DateOffset is selected.
Primary = AVERAGE ( 'Value by Date 1'[Value] ) Indicator = CALCULATE ( AVERAGE ( 'Value by Date 2'[Value] ), DATEADD ( DimDate[Date], -[DateOffset Value], MONTH ) )Regards,
Owen
Anonymous - I'm glad the original solution was useful to you as well!
If I understand your requirement correctly, you want to "shift" values from one category to another. Have I got that right?
If so, it would involve another parameter to allow selection of the category, and then using that within a measure to "allocate" values to the selected category.
Just to confirm, could you provide a visual example of how you expect it to behave? i.e. a mock-up of how the measures/visuals change when a given selection is made?
Regards,
Owen
Thank you for your answer OwenAuger!
Yes, the use case would be to play around with timelines of categories or projects to see what impact different scenarios would have on the overall KPIs like planned volumes for a given time period.
To illustrate what I mean here a mockup:
So e.g. the user would be able to select few categories and shift the timeline for only the selected category. In the mockup the first category would be delayed 2 years and in replace the second category comes 2 years earlier.
In the results section you would be then able to see the difference in the sum of volumes and maybe other KPIs like the different overall growth rates.
I know that sound pretty complex but your idea involving another parameter to add as context in the original measure sounds very promising 🙂
I assume the first step would be to create a measure for the category via the "selectedvalue" function... Where my creativity ends is how to implement/reference it in the original DAX measure so the calculation only happens for the selected category...
I tried this with the small multiples feature but it doesn´t work (sadly I´m not a DAX expert yet):
VAR Shifted Measure =
CALCULATE (
[Existing Measure],
DATEADD ( DimDate[Date], [Months To Shift Value], MONTH )
)
VAR For Selected Category=
IF(VALUES(ProductDim[Category] = [Selected Category Measure],
Shifted Measure,
[Existing Measure]
)
Return For Selected Category