Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

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

1 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

14 REPLIES 14
v-yulgu-msft
Employee
Employee

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

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.
Anonymous
Not applicable

See edit to my original post 🙂

OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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.

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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:

RLE_0-1664894545822.png

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

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

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

This is perfect, thank you, @OwenAuger!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.