Forum Discussion
Helper IICalculate Cumulative Count for Same Period Last Year
Hi Experts
I have a cumulative count measure that works, but now I need to do the same cumulative count for the same period last year and I am unable to get it
Can someone please help?
Cumulative Count = CALCULATE(COUNT(Tbl_Data_Register[ENTRY NUMBER]), FILTER(ALLSELECTED(Tbl_Data_Register),Tbl_Data_Register[DATE RAISED] <= MAX('Calendar'[Date])))
I have tried the below with no luck
Cumulative Count SPLY =
CALCULATE(
COUNT(Tbl_Data_Register[ENTRY NUMBER]),
FILTER(
ALLSELECTED(Tbl_Data_Register),Tbl_Data_Register[DATE RAISED]),
'Calendar'[Date] <= MAX('Calendar'[Date]) &&
'Calendar'[Date] >= MIN(DATESBETWEEN('Calendar'[Date], SAMEPERIODLASTYEAR('Calendar'[Date]), MAX('Calendar'[Date])))
)
)
Regards
Rah
rahul_ferns Use ALL instead of ALLSELECTED in the syntax also make sure calendar table and your data table have dates before 1 year. Also make sure tha the join date column should not have any timestamp. Like this:
Cumulative Count SPLY = var _date = MAX('Calendar'[Date]) var _max = date(year(_date)-1,month(_date),day(_date)) return CALCULATE(CALCULATE(COUNT(Tbl_Data_Register[ENTRY NUMBER]), FILTER(ALL(Calendar),Calendar[Date] <= _max)))You can follow the link below for more information regarding this issue:
If this helps you, please mark it as an accepted solution to help other users find it more easily.
5 Replies
devesh_guptaImpactful Individual
you can try one of the following two solutions and replace your measures:Solution1:
Cumm Sales = var _date = max('Date'[date])
Var _max = date(year(_date)-1, month(_date), day(_date))
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <= _max))Solution2:
Cumm Sales = var _date = max('Date'[date])
Var _max = date(year(_date)-1, month(_date), day(_date))
return
CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=_max))If this helps you, mark it as an accepted solution to help other members find it more easily.
rahul_fernsdevesh_gupta tried it with no luck. Getting no error, just blank column
Cumulative Count SPLY = var _date = MAX('Calendar'[Date]) var _max = date(year(_date)-1,month(_date),day(_date)) return CALCULATE(CALCULATE(COUNT(Tbl_Data_Register[ENTRY NUMBER]), FILTER(ALLSELECTED(Tbl_Data_Register),Tbl_Data_Register[DATE RAISED] <= _max)))- rahul_ferns
Tried changing to below with no luck. Again no erro, just blank column
Cumulative Count SPLY = var _date = MAX('Calendar'[Date]) var _max = date(year(_date)-1,month(_date),day(_date)) return CALCULATE(CALCULATE(COUNT(Tbl_Data_Register[ENTRY NUMBER]), FILTER(ALLSELECTED(Calendar),Calendar[Date] <= _max)))
- devesh_gupta
rahul_ferns Use ALL instead of ALLSELECTED in the syntax also make sure calendar table and your data table have dates before 1 year. Also make sure tha the join date column should not have any timestamp. Like this:
Cumulative Count SPLY = var _date = MAX('Calendar'[Date]) var _max = date(year(_date)-1,month(_date),day(_date)) return CALCULATE(CALCULATE(COUNT(Tbl_Data_Register[ENTRY NUMBER]), FILTER(ALL(Calendar),Calendar[Date] <= _max)))You can follow the link below for more information regarding this issue:
If this helps you, please mark it as an accepted solution to help other users find it more easily.
- rahul_ferns
Thanks, devesh