cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peter_Yaacoub
Frequent Visitor

Display Last month and December Last Year

Hi everyone, 

I am trying to build a balance sheet in a matrix visual showing the last month (i.e. October 2022 results) compared with December values of last year (i.e. December 2021) with the header reflecting Month & Year.

I have a date table with a date column marked as date.

 

I managed to calculate latest month and December last year's value through the following formulas. 

December =

VAR _mon = MONTH(LASTDATE('BS'[Date]))

RETURN

CALCULATE([Total Amount BS],DATESMTD(DATEADD('Date'[Date],-1*_mon,MONTH)))

 

Total Amount Last Date = CALCULATE([Total Amount BS],LASTDATE('BS'[Date]))

The desired outcome is to get the result to work in one measure. Appreciate the help!

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Peter_Yaacoub , Try measure like

 

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

 

 

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

 

 

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Peter_Yaacoub , Try measure like

 

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

 

 

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

 

 

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors