Total for SumX - no context

[ChrisWeldon]

First time poster asking for help, so if I have any problems with my post, please let me know.

I am trying to write a report to show the amount of premium for our contracts that is still in force, compared to the start of the year.

The data is held as follows:

tbl540

StatDt (Month End)	GrAky (Contract Number)	Premium
31/12/2018	1070	47,699
31/01/2019	1070	47,699
28/02/2019	1070	47,699
31/03/2019	1070	47,699
30/04/2019	1070	47,699
31/05/2019	1070	47,699
30/06/2019	1070	49,053
31/07/2019	1070	49,053
31/08/2019	1070	41,178
30/09/2019	1070	42,442
31/10/2019	1070	44,501
30/11/2019	1070	44,501
31/12/2019	1070	44,501

The year for analysis is set by a simple table containing years, with a Slicer over it.  The measures [StartDate] and [EndDate] are then generated.  In this case they are 31/12/2018 and 31/12/2019.  

The InforcePremium at any time is calcuated using the measure:

       InforcePremium=sumx(tbl540,tbl540[Premium]*related(tblWrUm[USD Ex Rate]))

I need to cap the premium at the value at the start of the year, so if the premium goes above that, the start value applies.  I use the measure:

      InforcePremiumNoNew=sumx(values(tbl540[GrAky]),if([InforcePremium]>[Inforce Premium At Period Start (all periods)],[Inforce Premium At Period Start (all periods)],[InforcePremium]))

where [Inforce Premium At Period Start (all periods)]=calculate(calculate([InforcePremium],filter(tbl540,tbl540[StatDt]=[StartDate],all(tbl540[StatDt]))

This gives the following when put into a table:

StatDt	InforcePremium	InforcePremiumNoNew
31/12/2018	47,699
31/01/2019	47,699	47,699
28/02/2019	47,699	47,699
31/03/2019	47,699	47,699
30/04/2019	47,699	47,699
31/05/2019	47,699	47,699
30/06/2019	49,053	47,699
31/07/2019	49,053	47,699
31/08/2019	41,178	41,178
30/09/2019	42,442	42,442
31/10/2019	44,501	44,501
30/11/2019	44,501	44,501
31/12/2019	44,501	44,501
TOTAL	601,420	47,699

So the main table values calculate correctly.  The total row gives me some issues.  

The total for [InforcePremium] is never actually used as it is a point in time value, so can be ignored.

The total for [InforcePremiumNoNew] needs to show the final value in the period.  But obviously the StatDt context is not there.  I can use the HasOneValue to seperate the StatDt level values and the totals, but not sure what the formula for the no StatDt context should be.  If I apply a filter(tbl540,tbl540[StatDt]=[EndDate]) then the result is 0.

Any ideas what it should be?

Many thanks.  If anyone needs more info, happy to try and help.

Chris

[sanalytics]

@ChrisWeldon 

Please provide some dummy data with your power bi model and your exact output..It will help us to solve your problem fast.One advice, please make your content brief..

Regards,

Sanalytics

[ChrisWeldon]

Hi Sanlytics, and thank you for your response.

Unfortunately our Corporate IT policy will not allow us access to any of the online storage facilities, or to email things like this to personal devices, hence the pasting of the relevant data into the tables.  

The solution I am looking for is how to get the mesaure InforcePremiumNoNew to show the last aviable value in the total row.

I hope someone can help.

Many thanks

Chris