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.