Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CloudMonkey
Post Prodigy
Post Prodigy

Cumulative Count for a given period

Hi,

 

I have to report on a KPI that requires a shop branch to carry out at least 1 marketing activity per month. So they don't get credit for a 2nd or 3rd marketing activity in a given month. Please can you tell me how to calculate a cumulative count of events for a given month and year (I will then later add up only the 1's). I'm basically trying to recalculate the below excel formula (as you can see the yellow cells returns 2 because branch 1 has already recorded a marketing activity in row 2).

 

Thanks

 

CM

 

 

1008am.JPG

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @CloudMonkey,

 

We should insert an index column in power query firstly.

 

Capture.PNG

 

Then we can create a calculated column as below.

 

Column = CALCULATE(SUM(Table1[Local Flag]),FILTER(ALLEXCEPT(Table1,Table1[year],Table1[Month],Table1[Activity],Table1[Branch Number]),Table1[Index]<=EARLIER(Table1[Index])))

2.PNG

 

Pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @CloudMonkey,

 

We should insert an index column in power query firstly.

 

Capture.PNG

 

Then we can create a calculated column as below.

 

Column = CALCULATE(SUM(Table1[Local Flag]),FILTER(ALLEXCEPT(Table1,Table1[year],Table1[Month],Table1[Activity],Table1[Branch Number]),Table1[Index]<=EARLIER(Table1[Index])))

2.PNG

 

Pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @CloudMonkey ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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