Forum Discussion
Post ProdigyYTD Attrition
I am trying to find the YTD Attrion. Below is the Sample data. I tried using Total YTD and DatesYTD function But these are not gving me the correct values. Please help.
| Month | Attrition |
| Jan-20 | 234 |
| Feb-20 | 678 |
| Mar-20 | 385 |
| Apr-20 | 434 |
| May-20 | 294 |
| Jun-20 | 557 |
| Jul-20 | 672 |
| Aug-20 | 508 |
| Sep-20 | 537 |
| Oct-20 | 516 |
| Nov-20 | 285 |
| Dec-20 | 667 |
| Jan-21 | 513 |
| Feb-21 | 611 |
| Mar-21 | 363 |
| Apr-21 | 570 |
| May-21 | 355 |
| Jun-21 | 249 |
| Jul-21 | 462 |
| Aug-21 | 264 |
| Sep-21 | 523 |
| Oct-21 | 360 |
| Nov-21 | 627 |
| Dec-21 | 423 |
Hi unnijoy ,
Do you want to calculate an accumulated result?
If yes, Plesae try the below measure:
measure = calculate(sum(value),filter(all(date),date <= max(date)))
Aiolos Zhao
8 Replies
Hi unnijoy ,
Do you want to calculate an accumulated result?
If yes, Plesae try the below measure:
measure = calculate(sum(value),filter(all(date),date <= max(date)))
Aiolos Zhao
- unnijoy
hi Anonymous
when i use the formula it is giving me diffrernt value. Till up to Feb 2020 it is giving me correct value. Like Jan 2020=234
Feb 2020= 234+678=912. But for March onwards it is giving different value.
For March as per your formula insted of March 2020=234+678+633=1545 I am getting 1321. How to fix this.
v-yingjlCommunity Support
Hi unnijoy ,
Based on your sample data, till up to March 2020, the value should be 234+678+385=1297. Where did 633 come from? Or there are other values in your dataset. Regardless of adding a calendar table to use totalytd() function or calculate values directly by the previous two post, both of them should work.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- amitchandak
Super User
unnijoy , Are you using time intelligence with Date calendar
Like
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Or my blog can helphttps://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885
Appreciate your Kudos. - Ashish_Mathur
