Time intelligence - Want to show MTD/Prev MTD, QTD and Prev QTD Dynamically.

[DrewSmith]

Hi,
I've built a Time Intelligence Table that links to a calendar date table. This allows me to have buttons on my report for MTD, QTD, YTD. 

I've linked that to my Sales Revenue and this works fine. 

However in a KPI card I want to show the reference value as last months/quarters/years figures, that dynamically change based on the time intelligence button. This doesn't seem to want to work and my presumption is that when I filter MTD it's filtering for 1st to 23rd Feb and then the previous figures don't have anything to look against. 

Can this be done? 

[DrewSmith]

My only guess Is I need to make a second table that has this years dates, last years dates, then the sum by date so of my time intelligence selects MTD then my second table is filtered for MTD and then sum that column. If it's QTD then use the third column etc. 

However I'd like to add in a second filter where they can select dates between X and Y dates but then how do I create that lookback period? 

[amitchandak]

@DrewSmith , You can create a field parameter with measures of MTD, QTD, and YTD

then you can create another measure based on that

So selected parameter you can get

Selected Parameter = maxx(filter(Parameter ,Parameter[Parameter Order] = SELECTEDVALUE(Parameter[Parameter Order])), Parameter[Parameter])

Then you use this code for another measure

Last period =

Switch([Selected Parameter],

"MTD", CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) ,

"QTD", CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,MONTH))) ,

"YTD", CALCULATE(SUM(Sales[Sales Amount]),DATESTTD(dateadd('Date'[Date],-1,MONTH)))

)

orginal measures

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD('Date'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]))

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf
Power BI Field Parameters, Keep Axis Sort intact| Always Sort on X/Categorial Axis: https://youtu.be/GfBrB6czByw

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s