Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
95 | |
91 | |
35 | |
29 |