Automatic relative date filter for YTD card

[Anonymous]

Hi there,

I have a YTD card in my report with a delta value card next to it.

The delta card has a relative date filter which is set to "in the last 1 calendar month" and shows the correct data. We update the dashboard on a monthly basis and I would like to not have to adjust the date filter each time. (from "1 calender month" to "2 calender months" and so on)

Is there any way to automate this? If I set the filter to "this year" the LYTD function will give out the complete data of last year and the delta will therefore not work correctly.

Here are my functions:

ImprYTD = TOTALYTD([Total Impressions],LU_DATES[Date])

ImprLYTD = CALCULATE([Total Impressions],PARALLELPERIOD(LU_DATES[Date],-12,MONTH))

ImprDelta = DIVIDE([ImprYTD], [ImprLYTD]) -1

I can provide more details on the underlying data if needed.

Thanks in advance

Leo

[Anonymous]

Hi @amitchandak 

I have added new measures based on the functions you provided and also created a new Delta measure from them. However, the Last YTD Measure still shows me the value of the full year (87 million). I tried using the relative date filter on it using "in this year" but it didn't change the outcome. 

Do you think the error could lie in the date table? I took a screenshot of it, hoping this gives some insight.

 

Best

Leo

[amitchandak]

@Anonymous , Make sure you are using date table, in measure, slicer and visual and measure like

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

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

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