Forum Discussion

GunnerJ's avatar
GunnerJ
Icon for Post Patron rankPost Patron
2 years ago
Solved

Cumulative Sum considering start and end dates

I'm trying to show the growth of our customer base and can pretty easily get the cumulative count based on connect date but realized that it's showing the all time connection and not who was active a...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi GunnerJ ,

    Check if this is your expected output:

     

    Cumulative Accts = 
VAR max_date = MAX('Date Table'[Date])
RETURN
    CALCULATE(
        COUNT('Elec Service All Time'[BI_ACCT]),
            'Elec Service All Time'[BI_CON_DT] <= max_date
            && 'Elec Service All Time'[BI_DISC_DT] > max_date
    )

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

GunnerJ's avatar
GunnerJ
Icon for Post Patron rankPost Patron
2 years ago

Greg_Deckler 
Thank you for the quick reply. 
I'm able to get the "connects" to work using your logic. However, I'm still stuck on the disconnect piece.

 

The first part which is currently commented out works to show adds per day in a cumulative sum.

I was playing around with having a disconnect version and subtracting it from the connects but without success.

*I'd tried switching the "max_date" value to connect date to stay the same *

Cumulative Accts = 
/*
var max_date = MAX('Elec Service All Time'[BI_CON_DT])
var table_A = FILTER(ALLSELECTED('Elec Service All Time'), 'Elec Service All Time'[BI_CON_DT] <= max_date)
   return  COUNTX(table_A, 'Elec Service All Time'[BI_ACCT]) */

var max_date = MAX('Elec Service All Time'[BI_DISC_DT])
var table_B = FILTER(ALLSELECTED('Elec Service All Time'), 'Elec Service All Time'[BI_DISC_DT] <= max_date)
return COUNTX(table_B, 'Elec Service All Time'[BI_ACCT])

 

Any advice as to how I could best incorporate the subtraction of disconnected accounts?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi GunnerJ ,

    Check if this is your expected output:

     

    Cumulative Accts = 
VAR max_date = MAX('Date Table'[Date])
RETURN
    CALCULATE(
        COUNT('Elec Service All Time'[BI_ACCT]),
            'Elec Service All Time'[BI_CON_DT] <= max_date
            && 'Elec Service All Time'[BI_DISC_DT] > max_date
    )

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • Joselete's avatar
      Joselete
      Frequent Visitor
      2 years ago

      Hi,

      thanks for the explanation, but I am also trying the same, using the max and min output of this slide in order to filter a table with cumulative counting. 

      The problem is, that it only works with the max_date as higher limit but not with the min_date as lower limit from the referenced Datetable

      Here when I try with 

      Append1[Ist_Stx56]<=max_date&&Append1[Ist_Stx56]>=min_date
      so the table is always filtered with the output dates from a visual

      as you can see it does not sum accumulative, just counts.

       


      But if I change for example the min_value for an specific date (first january for example isntead of an output from the date visual), it works the cumulative sum:

       

      here with specific date as minimum: