cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
faisalt
Frequent Visitor

Finding Previous year's Sales in Power BI with week number

I am trying to find the Previous year sales for the same week in Power BI.I dont have any date column. I have two table .One is FACT Indicators table as shown below: 

 

faisalt_2-1618379463993.png

and one sales table( Fact Sales table): 

 

faisalt_3-1618379503435.png

I want to create one calculated field namely(Sales Previous Year) to show the previous year sales for the same week .

In 'Fact Indicators' table 'PY 52 week flag' filed shows if this week id is Previous year or not.

Week column shows the week number from 1 to 52 weeks .

Week Id shows the unique number per Market key.

'Market_Week Id Key' is the common joining key between FACT Indicators table and Fact Sales table

Please help me to find the formula for calculated field.

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@faisalt , Week ID, Week and year move to an independent table (Say Date/week table)

 

Create a week rank on Week ID

Week Rank = RANKX(all('Date'),'Date'[Week ID],,ASC,Dense) //YYYYWW format

 

measures

This Week = CALCULATE(sum('sales'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('sales'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('sales'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

 

same week last year

 

This year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year  = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

 

 

YTD

 

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
amitchandak
Super User IV
Super User IV

@faisalt , Week ID, Week and year move to an independent table (Say Date/week table)

 

Create a week rank on Week ID

Week Rank = RANKX(all('Date'),'Date'[Week ID],,ASC,Dense) //YYYYWW format

 

measures

This Week = CALCULATE(sum('sales'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('sales'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('sales'[sales]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

 

same week last year

 

This year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year  = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

 

 

YTD

 

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors