Relative time dimensions combined with selectable measure

[AndySmith]

Hi all. I am trying to create a report that will show data with various relative time dimensions (YTD v LYTD, QTD v LQTD etc) in the columns but that will also use a dymanic measure slicer - so that the report would toggle between displaying Sales, Costs, or margin.

All  data currently in the one table. 

Is this possible?

[v-jingzhang]

Hi @AndySmith 

I think this is able to be achieved. You can create measures for Sales, Costs and Margins separately, then put them in MTD, LMTD, QTD, LQTD measures with SWITCH() function. Put Sales, Costs, Margins in an independent table for the measure slicer. When you select different values in the measure slicer, those time dimension measures will switch the measure according to the selected value. Finally put MTD, LMTD, QTD, LQTD measures into Values of a matrix visual and put Product / Brand or something like this into Rows.

Kindly let me know if this helps. You can also share some dummy data so that I can create a demo with it.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[AndySmith]

Many thanks @v-jingzhang. So each measure has to be created seperatley? 

I have attempted to attach dummy data below. Hope it works. So the desired outcome would be a matrix report that shows the below and the measures could be switched between Cost,Price,Margin

Selected Dimension (brand,product, state)	MTD	LMTD	QTD	LQTD	YTD	LYTD

https://jovalgroup-my.sharepoint.com/:x:/g/personal/asmith_joval_com_au/EXKnQlFFVl1Hgg10du-iyGQBzj23... 

[v-jingzhang]

Hi @AndySmith , please download this PBIX file.

For your question, the answer is Yes. I create measures for Cost and Margin separately to be involved in every YTD/LTYD/... measure. I don't involve Price in the demo because I didn't find Price data in your dummy data. If you want measures for Sales or Price, you can copy Cost or Margin measures and change the column name in them. Logic is the same.

To use the Time Intelligence functions in the measures, you need to create a Date or Calendar table and mark it as Date table. You can refer to the links in Amit's reply. I also involve it in the demo. Let me know if you have any questions.

Regards,
Jing
If this post helps, please consider Accept it as the solution to help other members find it.

[AndySmith]

That is a great solution. What I am trying to achieve is not only the time cals based on measure selection, but am also trying to use a slicer to toggle betyween dimensions. I created a similar report with a month on month date range - where the slicers control which measure and which dimension is used. So trying to recreate this but with relative time reporting instead of month on month. See example of existing report below:

[v-jingzhang]

@AndySmith

If you want to show the relative time measure in a column, you can create a table to include all measure names and use this column in a slicer. Then create a measure to switch the choice. Download PBIX file.

AllMeasure = SWITCH(SELECTEDVALUE('ALL'[Measures]),"LMTD",[LMTD],"LQTD",[LQTD],"LYTD",[LYTD],"MTD",[MTD],"QTD",[QTD],"YTD",[YTD])

Regards,

Jing

[AndySmith]

This is increible. Have managed to get this working on my data and have brought in a few different measures. Thanks for your help. One other question I have on thi though - is it possible to apply conditional formating to the numbers in the MTD,QTD,YTD measures? Some of the measures will be $ and others are a % - but I can only seem to filter to one type (as in $ or %)

[AndySmith]

Thanks @amitchandak 

What I would like to show is a matrix with the time dimensions in fixed columns ie MTD, LMTD, QTD, LQTD and have the measures within the columns change via a measure slicer. I have the measure slicer part of the puzzle but struggling to see whether its possible to do as described above with the fixed date dimension columns?

[amitchandak]

@AndySmith , you can use time intelligence for QTD and YTD

example

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

For switching measures, you can use measure slicer

measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://www.youtube.com/watch?v=vlnx7QUVYME

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0