Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MWinter225
Advocate IV
Advocate IV

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

InvoicedDateEarnedDateDollarAmountInvoiceNoCompany
11/2/201611/2/201710001Globex Corporation
12/14/201712/14/201825002Globex Corporation
6/12/20186/12/201915003Initech

 

CLAIM DOLLARS

ClaimDateClaimAmountInvoiceNoCompany
12/25/20163001Globex Corporation
3/5/2017501Globex Corporation
8/9/20187502Globex Corporation
9/27/20182003Initech
2/19/20192003Initech

 

and YEAR

YearYearStartYearEnd
20161/1/201612/31/2016
20171/1/201712/31/2017
20181/1/201812/31/2018
20191/1/201912/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   
CompanyInvoiceNoEarned PremiumClaim Amount
Globex Corporation1161.64300
    
Slicer=2017   
CompanyInvoiceNoEarned PremiumClaim Amount
Globex Corporation1835.6250
 2116.440
    
Slicer=2018   
CompanyInvoiceNoEarned PremiumClaim Amount
Globex Corporation22376.71750
Initech3830.14200
    
Slicer=2019   
CompanyInvoiceNoEarned PremiumClaim Amount
Initech3665.75200

 

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
v-chuncz-msft
Community Support
Community Support

@MWinter225 ,

 

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.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@MWinter225 ,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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