Need Help - YTD vs LYTD as of Today

[rp5005]

I have been struggling on what it is I am missing to compare last year sales data to this year sales data as of today.

My goal is to just put a card on my report that shows the percentage as of today of this year vs last year.

Percent Sales YTD vs Last = ([Total Sales YTD] - [Total Sales YTD Last Year]) / [Total Sales YTD Last Year] * 100

Total Sales = SUM(TestTable[InvoiceQTY])

Total Sales YTD = TOTALYTD([Total Sales],TestTable[DateFormatted].[Date])

Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR(TestTable[DateFormatted].[Date]))

My table shows my values and they appear to be in order, I see the cumulatives sales for the entire year.  My problem is I want my card to only compare this year to last year with the same day of the year as last.  Please help me see what I'm missing.

EDIT: I was able to take Amitchandak's reply and craft what I needed:

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

I was able to change this to match my scheme:

LYTD QTY Forced = var _max = date(year(today())-1,month(today()),day(today()))

return

CALCULATE(Sum(TestTable[InvoiceQty]),DATESYTD(dateadd(invhis[DateFormatted].[Date],-1,year)),invhis[DateFormatted]<=_max)

and then used this new value in my previous query:

Percent Sales YTD vs Last = ([Total Sales YTD] - [LYTD QTY Forced]) / [LYTD QTY Forced] * 100

Granted I may run into another issue by not following all the recommendations, but for today I am happy.

[amitchandak · Accepted solution]

@rp5005 , You have to force it for today, if you are not selecting date. Please use a calendar

YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

//Others

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

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))

YTD QTY forced 2= 
var _max = MONTH(today())
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),MONTH('Date'[Date])<=_max)



YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

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...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.


Appreciate your Kudos.

[AllisonKennedy]

First, you should use a DimDate table, rather than using the date from the same table: 

https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

Secondly, try DATEADD instead of SAMEPERIODLASTYEAR if you want day level granularity. 

[Greg_Deckler]

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-TIT...

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.