cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

YOY Calculation with Date Filter

Hello,

 

I'm looking to create a table which has Profit, Last Year Profit, Count of Sales and Last Years Count.

 

I'm using SamePeriodLastYear however I want to use a date filter so I can select specific date ranges, for example the table below shows 1st May - 25th May however unless the filter includes dates from last year then the values are blank. 

 

Should I use something else apart from SamePeriodLastYear? 

 

 Annotation 2020-05-26 182316.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: YOY Calculation with Date Filter

@mjohnsonvertu ,

Prefer to create a date table and use that for time intelligence. Have a week, month year etc in the date table.

 

sales =SUM(Sales[Sales Amount])

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

YOY = [sales]- [Year behind Sales]

 

YTD diff

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


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)


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)

// Year wise
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))

 

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


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User IV
Super User IV

Re: YOY Calculation with Date Filter

Hey @mjohnsonvertu ,

 

this article https://www.daxpatterns.com/time-patterns/ contains almost everything about date-based calculations inside Power BI using DAX

 

It will become necessary that you use a dedicated Calendar table (a best practice in Power BI data modeling). Here you will find an example of how you can create this table using DAX: https://github.com/sql-bi/DaxDateTemplate

 

The steps you have to accomplish:

  1. Create a Calendar table
  2. Establish a 1-to-many relationship between the Calendar (the one-side) and your table (the many-side)
  3. Create two measures, one for the Last Year Profit and one for the Last Years Count.

Hopefully, this provides what you are looking for to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: YOY Calculation with Date Filter

@mjohnsonvertu ,

Prefer to create a date table and use that for time intelligence. Have a week, month year etc in the date table.

 

sales =SUM(Sales[Sales Amount])

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

YOY = [sales]- [Year behind Sales]

 

YTD diff

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


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)


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)

// Year wise
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))

 

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


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Super User IV
Super User IV

Re: YOY Calculation with Date Filter

Hey @mjohnsonvertu ,

 

this article https://www.daxpatterns.com/time-patterns/ contains almost everything about date-based calculations inside Power BI using DAX

 

It will become necessary that you use a dedicated Calendar table (a best practice in Power BI data modeling). Here you will find an example of how you can create this table using DAX: https://github.com/sql-bi/DaxDateTemplate

 

The steps you have to accomplish:

  1. Create a Calendar table
  2. Establish a 1-to-many relationship between the Calendar (the one-side) and your table (the many-side)
  3. Create two measures, one for the Last Year Profit and one for the Last Years Count.

Hopefully, this provides what you are looking for to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Highlighted
Helper I
Helper I

Re: YOY Calculation with Date Filter

Hi both @amitchandak@TomMartens.

 

Thanks for your replies, worked a treat! I was wondering whether there was a way to line up the days to match previous year as well.

 

For example when I'm reporting this month it will be Friday the 1st of May however in 2019 the 1st of May is a Wednesday so I want to basically have the YOY report starting from Friday 3rd of May 2019 so I can compare like for like days too.

 

Thanks,

 

Mark

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors