Forum Discussion

DataStraine's avatar
DataStraine
Advocate 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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

    • DataStraine's avatar
      DataStraine
      Advocate I
      5 years ago

      Looks like this worked! Could you explain this part of the code for me? I'm having a hard time understanding why it's greather than 0


      Is to ensure that 7 months ago there was some billable count?

       

       

    • DataStraine's avatar
      DataStraine
      Advocate 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
        Super 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

         

    • DataStraine's avatar
      DataStraine
      Advocate I
      5 years ago

      Sorry I manually entered thid data. It's fixed. The forumual gave me worked for getting the data on the monthly level; however, it wouldn't display it correctly when aggregated

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        I do not know what you mean by "it wouldn't display it correctly when aggregated"