Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rahul_ferns
Helper II
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
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



 

1 ACCEPTED SOLUTION
devesh_gupta
Solution Supplier
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)))

 

You can follow the link below for more information regarding this issue:

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.

View solution in original post

5 REPLIES 5
devesh_gupta
Solution Supplier
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)))

 

You can follow the link below for more information regarding this issue:

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.

Thanks, devesh

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

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



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

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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