Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to shift date axis using what-if parameter?

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, thereb...
Need Help
Tutorial Requests
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
OwenAuger
Super User
1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.