cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Winnie0530
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).

Winnie0530_0-1632492463391.png

 

1 ACCEPTED SOLUTION
amitchandak
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])))

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

1 REPLY 1
amitchandak
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])))

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.