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

OwenAuger's avatar
OwenAuger
Super User
7 years ago

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

  • OwenAuger's avatar
    OwenAuger
    Super User
    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
      7 years ago

      This is perfect, thank you, OwenAuger!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • OwenAuger's avatar
        OwenAuger
        Super User
        5 years ago

        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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?