Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. Plea...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous,

 

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

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous ,

 

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

 

Regards,
Frank