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

Relative time dimensions combined with selectable measure

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?

8 REPLIES 8
v-jingzhang
Community Support
Community Support

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.

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)MTDLMTDQTDLQTDYTDLYTD   
          
          

 

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

 

Hi @AndySmith , please download this PBIX file.

012803.jpg

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.

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:2021-01-29 10_04_57-Window.png

@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])

020303.jpg020304.jpg

Regards,

Jing

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
Frequent Visitor

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
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.