Forum Discussion
Datediff v SPLY
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.
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
1 Reply
- amitchandak
Super User
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