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

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

Hi,

You may download my PBI file from here.

Hope this helps.

  • DataStraine's avatar
    DataStraine
    Icon for Advocate I rankAdvocate 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
    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

       

  • DataStraine's avatar
    DataStraine
    Icon for Advocate I rankAdvocate 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
      Icon for Super User rankSuper User
      5 years ago

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

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

        When I pulled in the formula you gave you, when I sliced the data by year, it wouldn't show anything before 2021. What I want to be able to do is show the total number of customers who curned in a particular year, or quarter, or month using the same 7 month logic. what I expect to see in my Table by year is 2 churned for 2020, 1 churned for 2019, 1 churned for 2021. Instead what is displayed is only 1 churned for 2021.

        PBI file showing the issue -