Mutltiple Period Comparisons

[heycheeeno]

Hey All,
I've been mulling over this question for a while now and still havent come to a suitable solution.

I have some sales data which spans the last seven or so years (see below for an example)

Item	Date Sold	Qty	Price
Item A	1/1/2019	1	$10
Item A	1/10/2018	1	$10
Item B	8/12/2017	2	$40

What I am trying to do is build a matrix whereby the user can select a time period (e.g. 1/1/2019 -> 1/1/2020) and then the report will display the last 5 periods in regards to aggregations of Qty and Price.

I have attempted it with SAMEPERIODLASTYEAR and by using Measures which employ the DateAdd feature with varying degrees of success.

Is there a way to do this? Or achieve something similar?

[amitchandak]

Did not get the 5 period means. You want to show the period side by side or five periods. But if you have date dimension. How can you show year, using datesytd or year behind measure

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"))
Last YTD complete 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))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

Or rolling period like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  
Rolling last 1 before 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Maxx('Date',dateadd('Date'[Date],-1,MONTH)),-1,MONTH))  
	Rolling 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-1,MONTH))  

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[heycheeeno]

@amitchandak - Thanks for the information, apologies I wasnt clearer.

For example if the user was to select the period of 1/01/2019 -> 31/12/2019

I would want to show data from the previous five periods.

1/01/2018 -> 31/12/2018

1/01/2017 -> 31/12/2017

1/01/2016 -> 31/12/2016

1/01/2015 -> 31/12/2015

1/01/2014 -> 31/12/2014

[amitchandak]

I think the first two options should work. If you have date table joined with sold date

//option1
Last Sales = CALCULATE(SUM(table[Qty]),DATESYTD(ENDOFYEARdateadd('Date'[Date],-1,Year),"12/31")))
2nd Last Sales = CALCULATE(SUM(table[Qty]),DATESYTD(ENDOFYEARdateadd('Date'[Date],-2,Year),"12/31")))
//Option 2
Year behind Sales = CALCULATE(SUM(Table[qty]),dateadd('Date'[Date],-1,Year))
2 Years behind Sales = CALCULATE(SUM(Table[qty]),dateadd('Date'[Date],-2,Year))
//Option 3
Year behind Sales = CALCULATE(SUM(Table[qty]),filter(all(Date),year(Date)=year(maxx(Date,Date[Date]))-1))
//Option 4
Year behind Sales = CALCULATE(SUM(Table[qty]),filter(all(Date),year(Date)=2019))