cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
Solution Supplier

@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
Solution Supplier

@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

Solution Supplier

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors