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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tsynenkyy
Helper II
Helper II

Filters Compare two years, first one selected, the second one automated

Hi guys,

 

I have the following two filters:

tsynenkyy_0-1665038496906.png

You select both years and the measures works perfectly, but the next step is to automatize the "Year & Month Comparison" so when I select "Year & Month" = 2022   then automatically the filter "Year & Month Comparison" will select 2021.

 

Relationship between both time tables:

tsynenkyy_2-1665038626297.png

thanks

2 REPLIES 2
amitchandak
Super User
Super User

@tsynenkyy , with help from a date table last year can be

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

 

 

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

Hi, many thanks for your asnwer but I think this is not what I need, actually I think that what I need is not possible in Power BI.

 

I need to keep the functionality I have (select both years) but when I firstly select the "Year and Month" it must automatically change the value on my other filter "year and month comparison) to YearSelected - 1, but I think is not possible that.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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