week over week calculation

Question

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 situationthere is a heirarchy which goes like thisRegion &gt; Trade &gt; Manager &gt; Supplier &gt; Part&nbsp;regionTrademanagersupplierpartspendR2T3M3S2P5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,051,229R2T3M1S2P7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,669,068R2T3M1S4P3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,473,159R2T1M2S1P1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,540,942R2T3M3S4P1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,691,689R2T2M3S3P2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,726,536R2T2M1S3P8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,836,488&nbsp;the heirarchical structure looks like this&nbsp;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 &gt; trade &gt; manager &gt; supplier &gt;part has shown the high variance.&nbsp;variance = current week actuals - previous week actuals&nbsp;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 &gt; trade &gt; manager &gt; supplier &gt;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).&nbsp;&nbsp;DimaMD&nbsp;amitchandak&nbsp;olgad&nbsp;DimaD&nbsp; Please help

dimamd · Answer

abhishekdas72&nbsp;Hi,&nbsp;Are dates present in your fact table?

abhishekdas72 · Answer

Yes they are present

dimamd · Answer

abhishekdas72&nbsp;Provide more detailed sample data, or attach a pbix file, without confidential data

dimamd · Answer

abhishekdas72&nbsp;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 &amp;&amp; 'dates'[Year] = currentYear -1,
     'dates'[Weekmun] = CurrentWeek - 1 &amp;&amp; 'dates'[Year] = currentYear)),
     [Total spend]
 )
&nbsp;second measure (example)
Previous week = 
 CALCULATE( [Total spend], 
 FILTER( ALL(dates) , dates[Weekmun] = SELECTEDVALUE(dates[Weekmun]) - 1 &amp;&amp; dates[Year] = SELECTEDVALUE(dates[Year])))
&nbsp;

&nbsp;

Forum Discussion

week over week calculation

4 Replies

Related content

Week Start calculation

Calculate change % week on week duration

Calculate previous week values

Calculating the week number

Calculate previous week total and previous 2 weeks total

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