Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @CloudMonkey,
We should insert an index column in power query firstly.
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])))
Pbix as attached.
Regards,
Frank
Hi @CloudMonkey,
We should insert an index column in power query firstly.
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])))
Pbix as attached.
Regards,
Frank
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |