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. 

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[Anonymous · Accepted solution]

Hi. Thanks everyone for your suggestions.

I found out the issue wasn't in the SPLY formula, where I simply used sameperiodlastyear calc. The Datediff formula was giving me grief and apparently you can't SUM the datediff, when I used AVERAGE datediff, it gave me the appropriate number and the measure then worked. 

[v-alq-msft]

Hi, @Anonymous 

You may try the following measure.

SPLY = 
var _currentyear = YEAR(MAX('Table'[Inception Date]))

return
CALCULATE(
    [Measure],
    FILTER(
        ALLSELECTED('Table'),
        YEAR('Table'[Inception Date]) = _currentyear-1
    )
)

Here is the result.

If I misunderstand your thought, please show me your expected result. Thanks.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

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

try this for years. Examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

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