Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])
)
)
)
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |