cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Regular Visitor

## Help! DATEDIFF Calculation with Different Tables

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Help! DATEDIFF Calculation with Different Tables

You may take a look at the post below.

https://community.powerbi.com/t5/Developer/Dax-Help-Create-table-with-variable-of-lines-according-to...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team

## Re: Help! DATEDIFF Calculation with Different Tables

You may take a look at the post below.

https://community.powerbi.com/t5/Developer/Dax-Help-Create-table-with-variable-of-lines-according-to...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.