cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Cumulative total based on Sponsor and show Lapse Yr when Cumulative >0

Hi All,

Need help on DAX for the below scenario

1.Need to calculate Cumulative total for Amount based on Lapse Yr and Sponsor

2.BEY-Cumulative total>0 (first postive value) need to show corresponding Lapse Yr for Sponsor,if Cumulative total<0 then 11

Expected Result:

Thanks,

Sanjay

1 ACCEPTED SOLUTION
Community Support

If you want to calculate Cumulative total for Amount based on Lapse Yr and Sponsor, try this measure:

``Cumulative Total = CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[SPONSOR]=MAX('Table'[SPONSOR])&&[LAPSE YR]<=MAX('Table'[LAPSE YR])))``

And if you want to calculate the BEY, you can try this measure:

``````BEY = var _BEY=MINX(FILTER(ALLSELECTED('Table'),[SPONSOR]=MAX('Table'[SPONSOR])&&[Cumulative Total]>0),[LAPSE YR])
RETURN IF(_BEY=BLANK(),11,_BEY)``````

Below is the result.

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.

2 REPLIES 2
Community Support

If you want to calculate Cumulative total for Amount based on Lapse Yr and Sponsor, try this measure:

``Cumulative Total = CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[SPONSOR]=MAX('Table'[SPONSOR])&&[LAPSE YR]<=MAX('Table'[LAPSE YR])))``

And if you want to calculate the BEY, you can try this measure:

``````BEY = var _BEY=MINX(FILTER(ALLSELECTED('Table'),[SPONSOR]=MAX('Table'[SPONSOR])&&[Cumulative Total]>0),[LAPSE YR])
RETURN IF(_BEY=BLANK(),11,_BEY)``````

Below is the result.

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.

Helper I

Hi Stephen,

Thanks its working ,will check other scenario and comeback if any help needed.

Thanks,

Sanjay.