Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
PBIX Report and Data found here: https://1drv.ms/f/s!AudOZTlxrIN8hdMQS7USlyVCU-EhNA
Hey all,
I have three tables:
PREMIUM DOLLARS
InvoicedDate | EarnedDate | DollarAmount | InvoiceNo | Company |
11/2/2016 | 11/2/2017 | 1000 | 1 | Globex Corporation |
12/14/2017 | 12/14/2018 | 2500 | 2 | Globex Corporation |
6/12/2018 | 6/12/2019 | 1500 | 3 | Initech |
CLAIM DOLLARS
ClaimDate | ClaimAmount | InvoiceNo | Company |
12/25/2016 | 300 | 1 | Globex Corporation |
3/5/2017 | 50 | 1 | Globex Corporation |
8/9/2018 | 750 | 2 | Globex Corporation |
9/27/2018 | 200 | 3 | Initech |
2/19/2019 | 200 | 3 | Initech |
and YEAR
Year | YearStart | YearEnd |
2016 | 1/1/2016 | 12/31/2016 |
2017 | 1/1/2017 | 12/31/2017 |
2018 | 1/1/2018 | 12/31/2018 |
2019 | 1/1/2019 | 12/31/2019 |
What I want to be able to do is use a slicer to filter on a single year in the YEAR table and then calculate the following logic. Calculating the Claim Dollars should be easy but I'm having an issue calculating the Premium Dollars.
Claim Dollars= IF the selected year in the YEAR table is the same as the ClaimDate year then show the SUM of the ClaimAmount.
e.g. the claim dollars for InvoiceNo 1 for 2016 would be $300
Premium Dollars is going to be more tricky. The DollarAmount for an InvoiceNo is dated on an InvoiceDate, but that premium isn't earned in total until a year from the day we receive it. So if you look at the data, InvoiceNo 1, we received $1,000 on 11/2/2016. We will earn an daily premium amount every day for a year ($1000 / 365 days in a year=$2.74 Daily Earned Premium Amount). I want to show the Yearly earned premium for a single year. e.g. That means the Yearly Earned Premium for InvoiceNo 1 in 2016 would be $161.66 ($2.74 daily earned premium * 59 days inbetween the InvoiceDate 11/2 and the end of that year 12/31). The Yearly Earned Premium for 2017 for InvoiceNo 1 would be $838.44 ($2.74 daily earned premium * 306 days in between the year start 1/1/2017 and the EarnedDate of 11/2/2017).
Premium Dollars=IF the selected year in the YEAR table is the same as the InvoiceDate year then calculate the DATEDIFF(InvoiceDate, YearEnd, DAY) and multiply that by the daily earned premium amount. IF the selected year in the YEAR table is the same ast the EarnedDate year then calculate the DATEDIFF(YearStart,EarnedDate,DAY) and multiply that by the daily earned premium amount.
My Goal is to have a matrix like this that will change every time a new single year is selected in the year slicer (based on the data above):
Slicer=2016 | |||
Company | InvoiceNo | Earned Premium | Claim Amount |
Globex Corporation | 1 | 161.64 | 300 |
Slicer=2017 | |||
Company | InvoiceNo | Earned Premium | Claim Amount |
Globex Corporation | 1 | 835.62 | 50 |
2 | 116.44 | 0 | |
Slicer=2018 | |||
Company | InvoiceNo | Earned Premium | Claim Amount |
Globex Corporation | 2 | 2376.71 | 750 |
Initech | 3 | 830.14 | 200 |
Slicer=2019 | |||
Company | InvoiceNo | Earned Premium | Claim Amount |
Initech | 3 | 665.75 | 200 |
The data I am working with is very large so I'd like to avoid using Power Query data maniplulations or at least keep them to a minimum. Check out the report link above to try out your solutions. Thanks for any input!
Matt
Solved! Go to Solution.
You may take a look at the post below.
You may take a look at the post below.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |