How can I sum total recurring revenue

[AFRATH34]

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 assist me in obtaining the sum of the measure values?

Thank you for your support.

Afrath

[AFRATH34]

Hi @mickey64 ,

I tried but it's not working, Please refer the screeshot and provide your sugguestion.

Thanks,

Afrath

[mickey64]

Did you add some relationships?

Are these relationships all active? 

[AFRATH34]

Hi @mickey64,

I didn't create any relationships, and even when I try to create them, it's not working.

Please find my PBIX attachment.

https://drive.google.com/file/d/1cF7fRfvWmAmTjsqd_0GzUiwstxrjZ1Ng/view?usp=drive_link 

Thanks,

Afrath

[mickey64]

I can't download your file.

[AFRATH34]

Hi @mickey64 ,

I have given access now, please check it. Sorry for the inconvenience.

Thanks,

Afrath

[mickey64]

Step 1: I add a relationship below.

Step 2: I reset the slicer and make a matrix below.

[AFRATH34]

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 · Accepted solution]

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]

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]

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-

[mickey64]

Please change ’Table’ visual into 'Matrix' visual.