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
Here's an example file:
http://s000.tinyupload.com/index.php?file_id=31701397748373985624
Thanks!
Hiram
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
This is perfect, thank you, OwenAuger!
Love that solution, would be great to see a reupload of your file (or from anyone else if OP doesn't respond)!
Anonymous
Just fixed the link in m post above 🙂
Thanks for pointing that out, as I will have to go back fix a bunch of others due to a change in my username.
4 years later and your post still helps people out 🙂 Thank you for your solution OwenAuger!
I would be interested if you have an idea how to analyse the use case even further: I have applied the approach of yours adding a category within the small multiple feature. The use case would be to move the date from 1 category and to "reschedule" it to a different category, to see the overall impact on the main KPIs like sum of profit of certain time period.
Do you think this is possible in any way?
4 years later and your post still helps people out 🙂 Thank you for your solution OwenAuger !
I would be interested if you have an idea how to analyse the use case even further: I have applied the approach of yours adding a category within the small multiple feature. The use case would be to move the date from 1 category and to "reschedule" it to a different category, to see the overall impact on the main KPIs like sum of profit of certain time period.
Do you think this is possible in any way?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 CategoryIf you have any tipps I would be really grateful, since the use case of "what happens if we move the timeline for category x" is a question we get all the time.
This is fantastic! Here is a question, I have a similar issue, but my x-axis isnt a date, its distance. How do I modify the "indicator" measure to take into account numbers (I have distance from 0 to 100 km)? To be honest, I am not sure sure why:
Indicator =CALCULATE (AVERAGE ( 'Value by Date 2'[Value] ),DATEADD ( DimDate[Date], -[DateOffset Value], MONTH ))
works, thats probably why I cant figure this out.Hi Anonymous
Glad to hear that this thread is still proving useful!
I've attached a more generic demo, using a Distance axis with range 0 to 100.
Quick description:
1. There is a parameter called Offset with measure Offset Value, with range -50 to +50.
2. The values are in the Data table, in columns Data[Value1] and Data[Value2], with associated measures Value1 Average and Value2 Average.
3. The Value2 Average Offset measure is defined as follows:
Value2 Average Offset = VAR OffsetValue = [Offset Value] VAR DistanceValuesOffset = TREATAS ( SELECTCOLUMNS ( Distance, "@DistanceOffset", Distance[Distance] - OffsetValue ), Distance[Distance] ) VAR Result = CALCULATE ( [Value2 Average], REMOVEFILTERS ( Distance ), DistanceValuesOffset ) RETURN ResultThis carries out the same operations as DATEADD did in the previous posts, however we have to write a little more code (since DATEADD and other time intelligence functions take care of this behind the scenes).
- The DistanceValuesOffset variable takes the visible values of Distance, subtracts the Offset, and treates the new values again as Distance. The reason for subtracting the Offset value is because a general definition of the function f(x) shifted to the right by k units is f_shifted(x) = f(x-k).
- In the Result variable, within CALCULATE, Value2 Average is calculated with
- REMOVEFILTERS clearing any filters on the entire Distance table (in case there are columns other than Distance itself).
- DistanceValuesOffset applied as the new Distance filters.
Hope that helps!
Regards
That is fantastic, and this such an elegant solution that works exactly with my dataset. Thank you so much for this, I was pulling my hair trying to refactor your dateadd one.
I further modified this to include a y-axis so I can move in both axis!
Added a similar numeric parameter for y-axis like you did for the x-axis, added a measure and modified the average offset measures.
Added Measure:Y-axis elev = (CALCULATE(MAX('OG_Data'[ELEVATION])) + 'Y-Offset'[Y-axis Value])
Modified Measure:elev Offset =VAR OffsetValue = [Offset Value]VAR DistanceValuesOffset =TREATAS (SELECTCOLUMNS (DimLegacyM,"@DistanceOffset", DimLegacyM[Value] - OffsetValue),DimLegacyM[Value])VAR Result =CALCULATE ([Y-axis elev],REMOVEFILTERS ( DimLegacyM ),DistanceValuesOffset)RETURNif (Result = [Y-axis Value],BLANK(),Result)
Only thing I didnt like is that if statement in the end that I had to do. I am sure there are better ways of dealing with it.
You are gentlemen and a scholar.