cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

New Member

## Total for SumX - no context

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:

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:

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

2 REPLIES 2
Solution Supplier

Regards,

Sanalytics

New Member

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors