week over week calculation

[abhishekdas72]

Hello Power BI Community,

I need to calculate week over week variance from a source table in SQL DB . Please help me out with the below situation

there is a heirarchy which goes like this

Region > Trade > Manager > Supplier > Part

region	Trade	manager	supplier	part	spend
R2	T3	M3	S2	P5	1,051,229
R2	T3	M1	S2	P7	1,669,068
R2	T3	M1	S4	P3	6,473,159
R2	T1	M2	S1	P1	3,540,942
R2	T3	M3	S4	P1	9,691,689
R2	T2	M3	S3	P2	3,726,536
R2	T2	M1	S3	P8	9,836,488

the heirarchical structure looks like this

What happens is each week the actuals are posted in same source table and i need to figure out a way to retain figures for the previous week only so that i can subtract from current week actuals and check which region > trade > manager > supplier >part has shown the high variance. 

variance = current week actuals - previous week actuals 

If suppose a part actuals has not been posted or there is no weekly history it should be added to the list under the same herirachy region > trade > manager > supplier >part.

i have connected to the SQL databse using import query method actuals are posted to the same table from which i am calculating the values .can this be done in power BI or this need something to be done at source level while importing the data or in SQL database( this is not an option as admin only grant generic READ only acess to the DB).  
@DimaMD @amitchandak @olgad @DimaD  Please help

[DimaMD]

@abhishekdas72 I have created an example, see the attached file and try to implement it in your report.
first measure (example)

1 - 
spend PW = 
 VAR CurrentWeek = SELECTEDVALUE(dates[Weekmun])
 VAR currentYear = SELECTEDVALUE(dates[Year])
 VAR maxWeeknum = CALCULATE( MAX( 'dates'[Weekmun]), ALL( dates))
 return
 SUMX( 
     FILTER( ALL(dates),
     IF( CurrentWeek = 1, 
     'dates'[Weekmun] = maxWeeknum && 'dates'[Year] = currentYear -1,
     'dates'[Weekmun] = CurrentWeek - 1 && 'dates'[Year] = currentYear)),
     [Total spend]
 )

 second measure (example)

Previous week = 
 CALCULATE( [Total spend], 
 FILTER( ALL(dates) , dates[Weekmun] = SELECTEDVALUE(dates[Weekmun]) - 1 && dates[Year] = SELECTEDVALUE(dates[Year])))

[abhishekdas72]

Yes they are present

[DimaMD]

@abhishekdas72 Provide more detailed sample data, or attach a pbix file, without confidential data

[DimaMD]

@abhishekdas72 Hi, Are dates present in your fact table?