Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |