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

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

 

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

 

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
Super User
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

@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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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