cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hfoster_p Frequent Visitor
Frequent Visitor

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

pri.PNGprimary timeseriesind.PNGindicator timeseries0.PNGwith no offset14.PNGwith 14 month offset (black is shifted to the right)

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: How to shift date axis using what-if parameter?

@hfoster_p

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




8 REPLIES 8
OwenAuger Super Contributor
Super Contributor

Re: How to shift date axis using what-if parameter?

@hfoster_p

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




v-yulgu-msft Super Contributor
Super Contributor

Re: How to shift date axis using what-if parameter?

Hi @hfoster_p,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hfoster_p Frequent Visitor
Frequent Visitor

Re: How to shift date axis using what-if parameter?

See edit to my original post Smiley Happy

hfoster_p Frequent Visitor
Frequent Visitor

Re: How to shift date axis using what-if parameter?

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

Re: How to shift date axis using what-if parameter?

@hfoster_p

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




hfoster_p Frequent Visitor
Frequent Visitor

Re: How to shift date axis using what-if parameter?

OwenAuger Super Contributor
Super Contributor

Re: How to shift date axis using what-if parameter?

@hfoster_p

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




hfoster_p Frequent Visitor
Frequent Visitor

Re: How to shift date axis using what-if parameter?

This is perfect, thank you, @OwenAuger!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 221 members 2,230 guests
Please welcome our newest community members: