totalytd sameperiod last year does not work properly

[Anonymous]

Hi,

I tried this in multiple manners but I cannot seem to get it to work.

As you can see in the picture below the year to date does not seem to work properly for me.

I want to have the amount of september, not the amount of december (last year)

So the lowest card should show until september (3rd) and not further.

Please let me know how I can do this.

[amitchandak]

@Anonymous , with a date table try 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))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

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


Appreciate your Kudos.

[Anonymous]

Hi all,

Thanks for the quick responses. I just made sure and try all your measures. So far they didn't work. Will continue and see if I can get the ones from greg to work.

Basically what happens here is:

I have a table with transactions, I sum those transactions like:

CALCULATE(SUM(Sales[Sales Amount]), FILTER(Class,LEFT(Class(Code,4)="xxxx"))*-1

Obviously it will take the full range of transactions, so I thought if I add DATESYTD to it it will take only the ones up to today.

Sadly it doesn't it will always SUM everything until the end of the year instead of until today.

Sample data is a bit difficult due to the size of this project but I'll try:

Transaction ID || Amount || Class || Date
1 || 10 || xxxx || 01-01-2020
2 || 10 || xxxx || 01-02-2020
3 || 10 || xxzz||  01-03-2020
4 || 10 || xxxx || 01-05-2020
5 || 10 || xxyy || 01-07-2020
6 || 10 || xxxx || 01-07-2020
7 || 10 || xxxx || 01-07-2020
8 || 10 || xxyy || 01-10-2020
9 || 10 || xxzz|| 01-11-2020
10 || 10 || xxxx || 01-12-2020

The outcome of my current situation of the sample data is: 100
the outcome that I want in this case is 70 (or 50 if you include the class filter). Because it should only go up to 03-09-2020
You can use the same data for last year, but just as 2019. It should be 70 but it shows as 100.

I use a date table that is in relation with this table.
I use a class table that is linked with this table

I hope this makes it a little bit more clear.

If you need more info please let me know and I'll try to provide it

[Anonymous]

Here an example of some sample data that works the same:

October should not be included since it's only september. So it should be 70 not 100

[Anonymous]

The workaround I found is:

Measure = 
var DaysLeft = DATEDIFF(TODAY(),ENDOFYEAR(Dates[Date]),DAY)
return 
TOTALYTD(SUM('Table'[Amount]),DATEADD(Dates[Date],-DaysLeft,day)

)

but is there not a simpler way of doing this?
EDIT: only works for this year.. Cannot use it for last year

[Fowmy]

@Anonymous 

The following are the formulas for YTD current and last year. make sure you have a calendar table

YTD = CALCULATE(SUM(Table[Amount]),DATESYTD('Calendar'[Date]))

YTD LY = CALCULATE(Table[YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Greg_Deckler]

@Anonymous You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.