Last Year Sales by day on a 4-5-4 Calendar

[aksharacoomar]

Hello, 

I am building a dashboard where I am required to build a table with day by day sales and add another column to represent the Sales $ for Last Year. Se attached for what the table will look like - 

I get my 'Net Revenue Actual' from my database but I am trying to figure out how I can add a column to calculate the Net Revenue for previous year for each date.

This is what my calendar table looks like - 

[V-lianl-msft · Accepted solution]

Hi @aksharacoomar ,

Please refer to the similar thread:

https://community.powerbi.com/t5/Desktop/Setup-a-custom-calendar-and-calculate-YTD-PTD-etc/td-p/1524... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@aksharacoomar 4-5-4 should be 364 days behind.

So with help from the date table, you can try like the example

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

In case of Week or any other period, if you can create Rank, that can give period last period and last year same period

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

https://www.youtube.com/watch?v=pnAesWxYgJ8

This Example of QTR, but true for all periods

Qtr Year  = year([Date]) & [Qtr]
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Year ],,ASC,Dense)	
Qtr Start Date = minx(filter('Date', [Qtr Year] = earlier([Qtr Year])),[Date])
Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1

measure 
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))
This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Day] <=max([Qtr Day])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day]))) 

[AllisonKennedy]

@aksharacoomar  You need a DimDate table: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

Then just use: 

Net Revenue = SUM[Database[NetRevenueActual])

PY Net Revenue = CALCULATE([NetRevenueActual], DATEADD(DimDate[Date], -12, month) )

Then use in your table from screenshot with columns from the DimDate table.