Forum Discussion
Weekly Reporting
Hi,
I'm looking for some help in using weekly totals for results made up from datestamped entries.
I need to show weekly summaries over time using sum/average etc. and it's simple to do this for a std week range (Mon-Sun or Sun-Sat) using weeknumber. What I'm looking for is some calculation which shifts the weekly pattern based on today's 'Day'.
For example, assuming results are available up to prior day, if I run the report on a Friday, the weekly history shows week levels based on a Fri-Thurs pattern. Similarly, if report run on a Wednesday, all prior 'weeks' would reflect the Wed-Tues picture.
This was something which was fairly straight forward using Qlik but I can't find a suitable method in Power BI.
Any help appreciated - Thanks
The week is rolling for 7 days. so rolling 7 days should help you for this week vs last week or if you filter on weekday and display dates. for each dates(filtered) you should get 7 days data.
This one should work for last 7 days. And you view by day and filter for weekday this should also work 7 days.
Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-7,DAY))
If needed
Rolling 7 before 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-7,DAY)),-7,DAY))
But for this create a date from timestamp and join it with date calendar
Date = [timestamp].date
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/These are bit different files , but should help you in creating calendar
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
https://www.dropbox.com/s/9agcbser2sqk02s/sales_analytics_weekWiseFriday_thru.pbix?dl=0
1 Reply
The week is rolling for 7 days. so rolling 7 days should help you for this week vs last week or if you filter on weekday and display dates. for each dates(filtered) you should get 7 days data.
This one should work for last 7 days. And you view by day and filter for weekday this should also work 7 days.
Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-7,DAY))
If needed
Rolling 7 before 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-7,DAY)),-7,DAY))
But for this create a date from timestamp and join it with date calendar
Date = [timestamp].date
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/These are bit different files , but should help you in creating calendar
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
https://www.dropbox.com/s/9agcbser2sqk02s/sales_analytics_weekWiseFriday_thru.pbix?dl=0