How to shift date axis using what-if parameter?

[Anonymous]

I have two timeseries that are connected by a DimDate table that I am plotting on a dual-axis line chart.

I would like to be able to use a What-If slicer to shift one time series by N months, thereby shifting the data points along the x-axis (date).

Any ideas of how I would go about doing this?

Edit:

Here is the layout of the data and an example of what I'm talking about.

primary timeseriesindicator timeserieswith no offsetwith 14 month offset (black is shifted to the right)

[v-yulgu-msft]

Hi @Anonymous,

Would you please provide more description about "use a What-If slicer to shift one time series by N months"? How is sample data? What is your desired output?

Regards,

Yuliana Gu

[Anonymous]

See edit to my original post 🙂

[OwenAuger]

@Anonymous

If you already have a measure and you want to shift the date context by a given number of months using a what-if parameter, the below pattern should do the trick.
The measure below evaluates your original measure with the date shifted by a given number of months.

I'll asuming you have

• Existing measure: [Existing Measure]
• Months to shift measure (value of what-if parameter): [Months To Shift Value]

Shifted Measure =
CALCULATE (
    [Existing Measure],
    DATEADD ( DimDate[Date], [Months To Shift Value], MONTH )
)

If [Months to Shift Value] is positive (negative), this will return the value of [Existing Measure] in a later (earlier) month, so will appear to shift the line to the left (right), so you may want to add a negative sign in front of [Months To Shift Value].

Regards,

Owen

[Anonymous]

Owen:

So once I do that, how do I create a relationship between the shifted measure and the DimDate? From what I can tell, you can't make relationships using a calculated field.

[OwenAuger]

@Anonymous

I am assuming there are existing relationships between the Date columns of the Primary & Indicator tables and DimDate[Date].

My suggested measure doesn't require any new relationships to be created - it just shifts the filter on DimDate[Date] forward or backwards, for the purpose of that measure only. This relies on there already being a relationship between the Indicator table (table containing the values to be "shifted") and DimDate.

Did you have a working PBIX file that you could post a link to (similar to the screenshots you added to your original post), and I could create a sample measure within that model?

Otherwise, I could mock up a model to illustrate the technique.

Regards,

Owen

[Anonymous]

@OwenAuger

Here's an example file:

http://s000.tinyupload.com/index.php?file_id=31701397748373985624

Thanks!

Hiram

[OwenAuger · Accepted solution]

@Anonymous

Here is my edited version of your PBIX.
link

1. First I created a parameter table DateOffset using Modelling => New Parameter.
2. Added a slicer for this parameter as in your screenshot
3. I also marked your DimDate table as a date table. This is best to do when you are using time intelligence functions.
4. 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

[Cookie_Dookie]

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.

[OwenAuger]

Hi @Cookie_Dookie 

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
    Result

This 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).

1. 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).
2. In the Result variable, within CALCULATE, Value2 Average is calculated with
   1. REMOVEFILTERS clearing  any filters on the entire Distance table (in case there are columns other than Distance itself).
   2. DistanceValuesOffset applied as the new Distance filters.

Hope that helps!

Regards

[Cookie_Dookie]

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

    )

RETURN

    if (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.

[Anonymous]

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? 

[OwenAuger]

@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

[Anonymous]

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

If 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.

[Anonymous]

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]

Love that solution, would be great to see a reupload of your file (or from anyone else if OP doesn't respond)!

[OwenAuger]

@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.

[Anonymous]

This is perfect, thank you, @OwenAuger!