Forum Discussion

Saarek's avatar
Saarek
Icon for Helper III rankHelper III
3 years ago
Solved

Cumulative Sum based on dates in Power BI

I have a set of claims data. This data only has three columns, TransactionDate, ClaimNumber, ReserveNet. I have been asked to pin point the maximum ReserveNet amount based on the dates group...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Saarek ,

     

    Since you post in Power BI Desktop forum. Here's the solution using DAX.

    You can create a measure to get the cumulative sum first.

    Cumulative = CALCULATE(SUM('Table'[ReserveNet]),FILTER(ALLSELECTED('Table'),[ClaimNumber]=MAX('Table'[ClaimNumber])&&[TransactionDate]<=MAX('Table'[TransactionDate])))

    Then create another measure to get the MAX cumulative sum.

    MAX Cumulative = MAXX(ALLSELECTED('Table'),[Cumulative])

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Saarek ,

 

Since you post in Power BI Desktop forum. Here's the solution using DAX.

You can create a measure to get the cumulative sum first.

Cumulative = CALCULATE(SUM('Table'[ReserveNet]),FILTER(ALLSELECTED('Table'),[ClaimNumber]=MAX('Table'[ClaimNumber])&&[TransactionDate]<=MAX('Table'[TransactionDate])))

Then create another measure to get the MAX cumulative sum.

MAX Cumulative = MAXX(ALLSELECTED('Table'),[Cumulative])

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

Saarek's avatar
Saarek
Icon for Helper III rankHelper III
3 years ago

Thank you very much, far simpler than I expected it to be. This works perfectly.