cancel
Showing results for
Did you mean: New Member

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

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

@Winnie0530 , 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])))

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!  Super User

@Winnie0530 , 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])))

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!   