Finding Previous year Sales in Power BI with week number

Question

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:

and one sales table( Fact Sales table):

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.I dont have the date field in my raw data

Answer 1

Every time you deal with anything related to dates, you will need to add what we call a date dimension. It will save you tons of headaches. Once you have it in you will be able to hook it into the creation of the calculated filed. you can google power bi or ssas date dimension and find tons of information on it.

Answer 2

Yeah! I guess SQL Technical team can be a tough crowd.... Well! In this case, I would recommend bringing the Year into FactSales Table from Fact Indicator . You have two options here with physical relationship set up between Market Week Id Key in both tables you can build a calc column with

Year  = CALCULATE(VALUES(FactIndicators[Year]))

or without relationship use LOOKUPVALUE on WeekId

Year = LOOKUPVALUE(FactIndicators[Year], FactIndicators[WeekId], FactSales[WeekId]) 

Sales Last Year calc colum :

SalesLastYear = 
 CALCULATE (
    SUM(FactSales[SalesThisYear] ),        
    TOPN(1,
   FILTER(           
       FactSales, 
       FactSales[Year] < EARLIER(FactSales[Year])
            && FactSales[Key] < EARLIER(FactSales[Key])
       )   
 )

)