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.
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.
4 Replies
- 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
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 
v-alq-msftCommunity Support
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.
- 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
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.
