Forum Discussion
Parallel Period not returning desired results
Hi,
I'm trying to find retention rate for YTD-2. I used sameperiodlastyear for YTD-1 and it worked:
= CALCULATE([# Active customers], Membership[Active LY-1]=TRUE(), SAMEPERIODLASTYEAR(DATESYTD('Calendar Transaction'[Date])))/CALCULATE(DISTINCTCOUNT(Membership[MembershipId]), Membership[Active LY-1]=TRUE())
but this formula isnt working when I try to do parallelperiod for YTD-2:
= (CALCULATE([# Active customers], Membership[Active LY-2]=TRUE(), PARALLELPERIOD('Calendar'[Transaction Date],-2,YEAR))/CALCULATE(DISTINCTCOUNT(Membership[MembershipId]), Membership[Active LY-2]=TRUE()))
The numbers I'm getting are just adding up to 100% (which they shouldn't be) for YTD-2 for some reason. I'm not having this same problem with YTD-1 values, they are correct.
Anyone know where I'm going wrong here?
Thanks!
Hi Anonymous ,
PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. For example, if you have a selection of dates that starts at June 10 and finishes at June 21 of the same year, and you want to shift that selection forward by one month then the PARALLELPERIOD function will return all dates from the next month (July 1 to July 31); however, if DATEADD is used instead, then the result will include only dates from July 10 to July 21.
So use "DATEADD" instead:
= (CALCULATE([# Active customers], Membership[Active LY-2]=TRUE(), DATEADD('Calendar'[Transaction Date],-2,YEAR))/CALCULATE(DISTINCTCOUNT(Membership[MembershipId]), Membership[Active LY-2]=TRUE()))Best Regards,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
- amitchandak
Super User
Anonymous , Try like measures like these
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"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year 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))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))Why TI fails - https://www.youtube.com/watch?v=OBf0rjpp5Hw
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA 
v-kelly-msftCommunity Support
Hi Anonymous ,
PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. For example, if you have a selection of dates that starts at June 10 and finishes at June 21 of the same year, and you want to shift that selection forward by one month then the PARALLELPERIOD function will return all dates from the next month (July 1 to July 31); however, if DATEADD is used instead, then the result will include only dates from July 10 to July 21.
So use "DATEADD" instead:
= (CALCULATE([# Active customers], Membership[Active LY-2]=TRUE(), DATEADD('Calendar'[Transaction Date],-2,YEAR))/CALCULATE(DISTINCTCOUNT(Membership[MembershipId]), Membership[Active LY-2]=TRUE()))Best Regards,
KellyDid I answer your question? Mark my post as a solution!
