How to calculate sum of sales amount for same period last year

[Anonymous]

Hi,

I have 3 tables in my model, Accounting period, Calendar and Sales.

Accounting period records the start and end date of each fiscal period. Calendar is generated nolmal calendar. Sales records every sales invoice date, customer, and amount.

Now I have slicer to select one period and one date in the period. How could I calculate sum of sales invoice amount for same period last year? For example I select year 2020, period 1, date 2020/2/5 which is the 3rd day of period 1, I want to calculate sum of invoice amount of year 2019 period 1 first 3 days (which is 2019/2/4 to 2019/2/6).

[amitchandak · Accepted solution]

@Anonymous , See like these are non standard period

You need rank on period YYYYPP

New columns

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

Period Day = datediff([Period Start Date], [Date], Day)+1

measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

TD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] <=max([Period Day])))
LPTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] <=max([Period Day])))