Need help with YTD calculation in DAX

[Anonymous]

Hello All,

I am trying to create a YTD measure which will sum the values under column plan_decimal. The values are repeating due to 2 unique accounts for each month and that column needs to be retained resulting in duplication of plan_decimal. 

Can someone help me with the DAX which can give the YTD sum based on this field and Date field.

This is what i tried but it adds up the value twice

TOTALYTD(SUM(Data[Plan_decimal]),Data[Date])

Expected result when date slicer is selected for:

March : 12.75

June :  12.75 + 12.75 (Mar)

Sep : 12.75 + 12.75 (Jun) + 12.75 (Mar)

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

The measure is

ytd = TOTALYTD(SUM(Sheet1[Plan_decimal]),Sheet1[Date])

Add a Account slicer to to distinguish A from B.

Or you can put your measure and Account column together in a table.

Another way is to determine whether Account is A or B in the measure.

ytd1 = TOTALYTD(CALCULATE(SUM(Sheet1[Plan_decimal]),FILTER('Sheet1',[Account]="A")),Sheet1[Date])

You can check more details from here.

Best Regards,

Stephen Tao

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

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Stephen Tao

[Anonymous]

Thanks a lot...sorry for the delay

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

The measure is

ytd = TOTALYTD(SUM(Sheet1[Plan_decimal]),Sheet1[Date])

Add a Account slicer to to distinguish A from B.

Or you can put your measure and Account column together in a table.

Another way is to determine whether Account is A or B in the measure.

ytd1 = TOTALYTD(CALCULATE(SUM(Sheet1[Plan_decimal]),FILTER('Sheet1',[Account]="A")),Sheet1[Date])

You can check more details from here.

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , Always use date table for that

TOTALYTD(SUM(Data[Plan_decimal]),'DAte'[Date])

refer my video why Time intelligence can fail - https://www.youtube.com/watch?v=OBf0rjpp5Hw

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 :radacad sqlbi My Video Series Appreciate your Kudos.

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

[Anonymous]

@amitchandak : Thanks for your reply. I had tried this but the issue is that the value is getting added twice since there are two rows correpoding to each month.

[amitchandak]

@Anonymous , Try this inplace of sum(Table[Plan_decimal])

sumx(values(Date[Date]), calculate(Max(Table[Plan_decimal])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

can you try downloading the pbix from this link

https://drive.google.com/file/d/1jROJXgUMrwrx0XKxsFH_PNfvEsgwkhzF/view?usp=sharing

[Anonymous]

@amitchandak I am trying to upload the pbix file but can't attach it here