Datediff v SPLY

[Anonymous]

Hi all,

Essentially, what I'm trying to do is calculate the premium that has been written, and exclude the premium that occurred as a result of the cancellation of the policy but I'm getting a really big weird number for SPLY and current year looks okay. 

Measure = SUM(Table[Premium]) - CALCULATE(SUM(Table[Premium]) / 365 * (365 - SUM(Table[DateDiff])), Table[Cancel Date]>0 )

Where DateDiff = Calculated Column = DATEDIFF( Table[Inception Date], Table[Cancel Date], DAY) 

Policy Number	Inception Date	Cancel Date	Premium	DateDiff
A	01/01/2019	30/06/2019	$100	Days: Incept - Cancel
B	01/02/2019	25/10/2019	$300	As above
C	05/01/2020	10/03/2020	$500	As above
D	04/02/2020	-	$600	As above

When I do the SPLY formula, I'm doing

SPLY = CALCULATE ( Measure , SAMEPERIODLASTYEAR ( Calendar ( date ) )

Is this correct, or am I doing it wrong because I'm getting an incorrect huge number for 2019, but 2020 looks okay. 

Thanks for any help. 

[amitchandak · Accepted solution]

Please note the measure need row context. Please check this file on how you can take datediff and keep the context. there are 4 ways.

As they are single table , so you can leave one where copy them to common table

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin