cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Need Help - YTD vs LYTD as of Today

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

I was able to change this to match my scheme:

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

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.
1 ACCEPTED SOLUTION
Super User

@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

//Others

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
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

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://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...

3 REPLIES 3
Super User

@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

//Others

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
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

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://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...

Super User

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Super User

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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements