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

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 - 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        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