Show past 1 & 2 months

[Begbie]

Hi community,

I'm trying to achieve the below in a Power BI report to show the spend for the past month and the month before that to show the growth of spend:

The data I have is:

The data has dates going back a year currently.

But when I use the query of:

Month-1 = CALCULATE(SUM('TopPlatformActualsDetails'[spend]),FILTER(ALL('TopPlatformActualsDetails'),MONTH('TopPlatformActualsDetails'[month])=MONTH(SELECTEDVALUE(TopPlatformActualsDetails[month]))-1))

This is still giving me the total spend all of months and not just the past one month. Maybe I have over complicated the query? 

I have also tried the following and it seems to give the same results:

Last Month Spend = CALCULATE(SUM(TopPlatformActualsDetails[spend]),PREVIOUSMONTH(TopPlatformActualsDetails[month].[Date]))

I'm sure there is a simple solution to this, but for the life of me cannot work it out.

Thank you 😊

[amitchandak]

@Begbie , always use date table for such cases

Rolling 2 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = Date(Year(_max), month(_max) -2, Day(_max))+1,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

This month = CALCULATE([Net],DATESMTD(ENDOFMONTH('Date'[Date])))

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-2,MONTH))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Begbie · Accepted solution]

Thanks for your help Amit. I managed to find something that you posted elsewhere which has worked for me, which was:

The previous month I need to do:

Previous Month = CALCULATE(sum(TopPlatformActualsDetails[spend]),DATESINPERIOD(TopPlatformActualsDetails[month],MAX(TopPlatformActualsDetails[month]),-1,MONTH))

For the month before, I needed:

Measure =
CALCULATE (
SUM ( Table[Amount] ),
DATESINPERIOD ( Calendar[Date], eomonth(MAX ( Calendar[Date] ),-1), -2, MONTH )
)