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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Pbiuserr
Post Prodigy
Post Prodigy

compare KPI to the last year end of year

Hello,

I want to compare KPI from given period to last year - end of the year. So for instance on slicer I got May 2022, it shows me lets say Sales Amount and I want to compare it to the last year (so May 2021) but shifted to December 2021

I guess its going to be mix of dateadd and endofyear functions? Can anybody help me with that?

 

1 ACCEPTED SOLUTION

Okay, my solution is:

 

VAR _endofyeardate = MAXX('Calendar',ENDOFYEAR(dateadd('Calendar'[Date],-12,MONTH)))
RETURN
Calculate([Measure], 'Calendar'[Date] = _endofyeardate)

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Pbiuserr , refer these measures based on today

 

using TI, selection of date is needed

last year last month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1*month(Today()),MONTH)))

 

No date selection needed

 

This Month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

Last Month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

Same Month Last Year Today =
var _min = eomonth(today(),-13)+1
var _max = eomonth(today(),-12)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

Last Month Last year Today =
var _min = eomonth(today(),-1*month(Today())-1)+1
var _max = eomonth(today(),-1*month(Today()))
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

I don't understand them

I want to compare value from user slicer selection (let it be April 2022) to December 2021
then (Value April 2022 - Value December 2021)/Value Dec 2021 in another measure

, then user selects June 2021 and it compares value to December 2020

then (Value June 2021 - Value December 2020)/Value Dec 2020 in another measure

does this measure solve that?

 

Okay, my solution is:

 

VAR _endofyeardate = MAXX('Calendar',ENDOFYEAR(dateadd('Calendar'[Date],-12,MONTH)))
RETURN
Calculate([Measure], 'Calendar'[Date] = _endofyeardate)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.