Forum Discussion

AFRATH34's avatar
AFRATH34
Icon for Helper I rankHelper I
2 years ago
Solved

How can I sum total recurring revenue

Hi Anonymous    I hope you're doing well. The solution you provided in our previous discussion is working fine. However, I'm having trouble getting the sum of the measure column. Could you please a...
Need Help
  • mickey64's avatar
    mickey64
    2 years ago

    Is the 'total number of month' '19'? (I think it is '17'.)

    I have created some measures but I am not sure if my measures are correct.

     

    Step 1: I unpivot 'Table' on Power Query Editor.

     

    Step 2: I add 2 columns on Power Query Editor.

    - 'Flag' column -

    - 'Date' column -

     

    Step 3: I make 2 measures and make a matrix below.

    M_RunningFlag2 = CALCULATE(SUMX('Table','Table'[Flag]*'Table'[Recurring Gross Profit per Period]),FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

     

    M_RF2 Sum = SUMX(SUMMARIZE('Calendar','Calendar'[Month and Year],"M_RF2",[M_RunningFlag2]),'Table'[M_RunningFlag2])

     

     

AFRATH34's avatar
AFRATH34
Icon for Helper I rankHelper I
2 years ago

Hi mickey64 ,

 

Thanks a lot for your support and response but the output is incorrect. My condition is to calculate the recurring profit per period from the billing date to the expiry date, and it should be displayed for every month within this range. For example, if the billing date is 16th June 2024 and the expiry date is 25th October 2025, the total number of months is 19. Therefore, the recurring profit should be calculated as 162 multiplied by 19, resulting in an output of 3078 when the user selects the date range from June 2024 to October 2025.

I think you have understood my requirements. Could you please provide some suggestions or help on how to achieve this calculation in Power BI?

 

Thanks,

Afrath

 

  • mickey64's avatar
    mickey64
    Icon for Super User rankSuper User
    2 years ago

    Is the 'total number of month' '19'? (I think it is '17'.)

    I have created some measures but I am not sure if my measures are correct.

     

    Step 1: I unpivot 'Table' on Power Query Editor.

     

    Step 2: I add 2 columns on Power Query Editor.

    - 'Flag' column -

    - 'Date' column -

     

    Step 3: I make 2 measures and make a matrix below.

    M_RunningFlag2 = CALCULATE(SUMX('Table','Table'[Flag]*'Table'[Recurring Gross Profit per Period]),FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

     

    M_RF2 Sum = SUMX(SUMMARIZE('Calendar','Calendar'[Month and Year],"M_RF2",[M_RunningFlag2]),'Table'[M_RunningFlag2])

     

     

  • AFRATH34's avatar
    AFRATH34
    Icon for Helper I rankHelper I
    2 years ago

    Hi mickey64 ,

     

    Thanks a lot for your valuable response and If we unpivot the 'Table' in Power Query Editor, will the data be duplicated?
    Also, please explain how to create the unpivot 'Table' in Power Query Editor.

     

    Thanks,

    Afrath

  • mickey64's avatar
    mickey64
    Icon for Super User rankSuper User
    2 years ago

    Unpivoting will result in duplicate data, but because the SUMMARIZE function creates a table with no duplicate data and performs calculations, there will be no duplicates in the calculation results.

     

    - Before Unpivot-

    - After Unpivot-