Forum Discussion

DataStraine's avatar
DataStraine
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Cumulative Sum When Not Desired?

Hello all. I'm very new to Power BI and I'm stumped. I am trying to wirte a calculation to determine where a customer is considered churned on their 7th month of inactivity. I have calculations for I...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    There should definitely be Month name in the visual for the measure to get a row context.  Write this additional measure and drag it to your matrix visual

    Measure 2 = SUMX(GENERATE(VALUES('Calendar'[Month name]),VALUES('Calendar'[Year])),[Measure])

    You may drag the Customer Name off the matrix, if you so wish

DataStraine's avatar
DataStraine
Icon for Advocate I rankAdvocate I
5 years ago

Oh, after playing around with displaying the values in the table it is now not aggregating the totals across years. See example

Monthly doesn't does appear to show any churned customers before May

 

When aggregated by year it doesn't show anything before the current year when it should show 1 customer churned in 2020 based on the above chart



Edit - I've edited the Sheet I shared above with more generic data and expected soultions based on that data. Essentially, when I slice the results by month, quarter, and year I need the totals to reflect what actually happened and have the data displayed for all of time up to present date

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Your results in column D of the expected results worksheet are wrong.  For id2, the 7th month of inactivity is March 2020 (not Feb 2020).  Likewise for id4, the answer should be October 2019.  Also, check the date in cell A44 - that seems wrong.  When i plug in your revised data into my file, this is the result i get