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

[sanjaymithran]

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

[v-stephen-msft · Accepted solution]

Hi @sanjaymithran ,

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.           

[sanjaymithran]

Hi Stephen,

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

Thanks,

Sanjay.