Forum Discussion

abhishekdas72's avatar
abhishekdas72
Frequent Visitor
3 years ago

week over week calculation

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 >...
Need Help
Tips and Tricks
DimaMD's avatar
DimaMD
Solution Sage
3 years ago

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])))