Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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", 11/05/2017, 10/29/2017, 10/22/2017.
Solved! Go to Solution.
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])
Take a look at my Week Starting/Ending measures in the Quick Measures Gallery. Should get you what you need to filter your data correctly. If not, I can make the modifications to do this most likely.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487
Sample data would help me come up with a specific solution. 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
This is exactly what i am trying to create. So if Select 3/10 /2018 from my slicer i would wanted the rolling measures to display all weeks before it
OK, you need slight variations of my Week Start and Week End measures plus the ones contained in the attached file and listed below.
Rolling Week Start
Rolling Week Start = VAR DateFrom = MAX([Date]) VAR WeeksBack = 4 VAR tmpCalendar = CALCULATETABLE('Calendar',ALL('Calendar')) VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"WeekNum",WEEKNUM([Date]),"WeekEnding",[mWeekEnding],"WeekStarting",[mWeekStarting]) VAR LookupDate = DateFrom-7*WeeksBack VAR tmpCalendar2 = FILTER(tmpCalendar1,[Date]=LookupDate) VAR retValue = MAXX(tmpCalendar2,[WeekStarting]) RETURN IF(ISBLANK(retValue),MINX(tmpCalendar1,[WeekStarting]),retValue)
Rolling Week End
Rolling Week End = VAR DateFrom = MAX([Date]) VAR WeeksBack = 0 VAR tmpCalendar = CALCULATETABLE('Calendar',ALL('Calendar')) VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"WeekNum",WEEKNUM([Date]),"WeekEnding",[mWeekEnding],"WeekStarting",[mWeekStarting]) VAR LookupDate = DateFrom-7*WeeksBack VAR tmpCalendar2 = FILTER(tmpCalendar1,[Date]=LookupDate) RETURN MAXX(tmpCalendar2,[WeekEnding])
I posted a better/more complete version here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128
Thanks. Where would I add the sum of sales in this mix?
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...
Thanks. This is a direct query from tabular model. so I am limited with options.
So, is it as simple as:
WeekEnding, Sales
In a single table or ?
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |