Forum Discussion
Monthly Rolling Year Count
I am wanting to display a rolling year count by month in a table visual.
Jan2023 would count all records from Jan2022-Jan2023.
Feb2023 would count all records from Feb2022-Feb2023.
This is my forumula. Not every elegant, I know.
_R12M Machines in Warranty =
VAR _1stOfMonthYearAgo =
DATE (
YEAR ( MIN ( dimDate[Date] ) ) - 1,
MONTH ( MIN ( dimDate[Date] ) ),
1
)
VAR _R12MCount =
CALCULATE (
DISTINCTCOUNT ( MachineStats[_Model-Serial] ),
FILTER (
MachineStats,
MachineStats[StartUpDate] >= _1stOfMonthYearAgo
&& MachineStats[_WarrantyEnd] >= MAX ( dimDate[Date] )
)
)
RETURN
_R12MCountThese are the two tables that I am using.
When I use my formula, it counts only the records for the current month, not the past year.
Any help would be appreciated.
haleswd258 , Based on what I got , In example I am using net measure and DimDate is date
You can use measures like
Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-13,MONTH))
Rolling 13= CALCULATE([Net], WINDOW(-12,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))
2 Replies
- amitchandak
Super User
haleswd258 , Based on what I got , In example I am using net measure and DimDate is date
You can use measures like
Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-13,MONTH))
Rolling 13= CALCULATE([Net], WINDOW(-12,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))
Thanks amitchandak
The calculation I ended up using was:CALCULATE(DISTINCTCOUNT ( MachineStats[_Model-Serial] ),DATESINPERIOD(dimDate[Date],MAX(dimDate[Date]),-13,MONTH))
