Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@Anonymous
Here is my edited version of your PBIX.
link
Primary = AVERAGE ( 'Value by Date 1'[Value] ) Indicator = CALCULATE ( AVERAGE ( 'Value by Date 2'[Value] ), DATEADD ( DimDate[Date], -[DateOffset Value], MONTH ) )
Regards,
Owen
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
See edit to my original post 🙂
@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
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:
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
Here's an example file:
http://s000.tinyupload.com/index.php?file_id=31701397748373985624
Thanks!
Hiram
@Anonymous
Here is my edited version of your PBIX.
link
Primary = AVERAGE ( 'Value by Date 1'[Value] ) Indicator = CALCULATE ( AVERAGE ( 'Value by Date 2'[Value] ), DATEADD ( DimDate[Date], -[DateOffset Value], MONTH ) )
Regards,
Owen
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
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
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?
Love that solution, would be great to see a reupload of your file (or from anyone else if OP doesn't respond)!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |