cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Calculate 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

``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

1 ACCEPTED SOLUTION
Super User

@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)))``````

https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

If this helps you, please mark it as an accepted solution to help other users find it more easily.

5 REPLIES 5
Super User

@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)))``````

https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

If this helps you, please mark it as an accepted solution to help other users find it more easily.

Helper II

Thanks, devesh

Super User

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_ferns

Helper II

@devesh_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)))``````

Helper II

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)))``````

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors