YTD Attrition

[unnijoy]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@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-Y...
Or my blog can help

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

Appreciate your Kudos.

[Anonymous · Accepted solution]

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.

[Anonymous]

Hi @unnijoy ,

In your sample, 

Mar-20

385

March 2020 is 385, so you will get 234 + 678 + 385 = 1297, right?

Aiolos Zhao

[unnijoy]

@Anonymous ,

This issue was with the uploaded data. I refresh the data and i got the values correct. Thanks for your help.

[Anonymous]

glad to help you @unnijoy 

Could you please set my answer as the solution so other people will know the thread is solved.

Thanks.

AiolosZhao

[v-yingjl]

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 Li

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