Forum Discussion
Rolling weeks calculation
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])
Yes All i would need is Sum(sales) for rolling 4 weeks. The Strtdate is from Fiscalcalendar and Sales is from Fact joined by calendar date.
OK, I really need sample data so that I can get this right. What does your fiscal calendar table look like? Is it just a standard calendar table like that generated by CALENDAR() function?
And then I assume that your sales table would have something in it like:
Sales table
Date,Sales
4/1/2018,1000
4/1/2018,500
4/2/2018,300
4/2/2018,100
etc.
And you have a relationship like:
Calendar 1->*Sales
Is this all correct?
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])Yes. Thats exactly it.