Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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:
For the month before, I needed:
Measure =
CALCULATE (
SUM ( Table[Amount] ),
DATESINPERIOD ( Calendar[Date], eomonth(MAX ( Calendar[Date] ),-1), -2, MONTH )
)
@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
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:
For the month before, I needed:
Measure =
CALCULATE (
SUM ( Table[Amount] ),
DATESINPERIOD ( Calendar[Date], eomonth(MAX ( Calendar[Date] ),-1), -2, MONTH )
)
User | Count |
---|---|
53 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
80 | |
57 | |
40 | |
19 | |
10 |