Forum Discussion

sgsukumaran's avatar
sgsukumaran
Resolver II
8 years ago
Solved

Rolling weeks calculation

I am tring to calculate rolling 4 weeks based on selected date from slicer. The slicer has only week ending date. So If I select "11/19/2017), I would  I ideally want to see sales for "11/12/2017", 1...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    OK, see attached PBIX Page 2. I added a Sales table and created a "Rolling Sales" measure. And graphed it.

     

    Rolling Sales = 
VAR rollweekstart = [Rolling Week Start]
VAR rollweekend = [Rolling Week End]
VAR tmpTable = ALL('Sales')
VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollweekstart&&[Date]<=rollweekend)
RETURN SUMX(tmpTable1,[Value])

     

     

sgsukumaran's avatar
sgsukumaran
Resolver II
8 years ago

Thanks. Where would I add the sum of sales in this mix?

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

That would be the Measure formula that I posted in the Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694

 

So, something along the lines of:

 

Measure = 
VAR rollweekstart = [Rolling Week Start]
VAR rollweekend = [Rolling Week End]
VAR tmpTable = ALL('Calendar')
VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollweekstart&&[Date]<=rollweekend)
RETURN SUMX(tmpTable1,[Value])

 

If you could give me a sense of your source data, that would greatly help. See this post here: Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490