Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
Anonymous
Not applicable

Automatic relative date filter for YTD card

Hi there,

 

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

Bildschirm­foto 2023-02-02 um 15.44.37.png

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

2 REPLIES 2
Anonymous
Not applicable

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.

Bildschirm­foto 2023-02-03 um 09.55.51.png

 

Bildschirm­foto 2023-02-03 um 09.52.18.png

Best

Leo

amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.