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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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

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

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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