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,
I need the follow formula as a measure :
( Current Month / Last Year December ) - 1 = Result
The idea is I can compare i.e. the data for march 2001 vs december 2000, or september 2005 vs december 2004. Every Month I select vs its last year december. All in the same table. How can I do that?
Thanks for your help.
Solved! Go to Solution.
Hi,
Try this
=(SUM(Data[Amount])/CALCULATE(SUM(Data[Amount]),DATESBETWEEN(Calendar[Date],DATE(YEAR(MIN(Calendar[Date]))-1,12,1),DATE(YEAR(MIN(Calendar[Date]))-1,12,31))-1
There must be a calendar table. There should be a relatiosnhip from the Date column of the Data Table to the Date column of the Calendar Table. In the Calendar Table, write these calculated column formulas to extract Year and month.
=YEAR(Calendar[Date])
=FORMAT(Calendar[Date],"MMMM")
In the visual you create, drag Year and Month in the Filter/slicer and select any one Year/Month.
Hi,
Try this
=(SUM(Data[Amount])/CALCULATE(SUM(Data[Amount]),DATESBETWEEN(Calendar[Date],DATE(YEAR(MIN(Calendar[Date]))-1,12,1),DATE(YEAR(MIN(Calendar[Date]))-1,12,31))-1
There must be a calendar table. There should be a relatiosnhip from the Date column of the Data Table to the Date column of the Calendar Table. In the Calendar Table, write these calculated column formulas to extract Year and month.
=YEAR(Calendar[Date])
=FORMAT(Calendar[Date],"MMMM")
In the visual you create, drag Year and Month in the Filter/slicer and select any one Year/Month.
Hi @Ashish_Mathur,
That's exactly what Im looking for. Thank you very much. Happy new year.
Hi,
Happy New Year to you as well. You are welcome.
hi michael
You can calculate the averages using CALCULATE () with FILTER and the DATAADD function.
I think that will do.
DATAADD( )
https://msdn.microsoft.com/en-us/library/ee634905.aspx
ex...
Contains :=
calculate(
Sum([value]),
Filter(
All('Date'[date]),
Contains(
values('Date'[date]),
'Date'[date], date(year('Date'[]date)), month('Date'[date])-1, day('Date'[date])
)
)
)
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |