So this is my first post, I just joined and am in the middle of getting power BI setup in my company – the tutorials I’ve watched here have been GREAT but I’m struggling to solve the below and hoping someone has some sort of solution for it… also hopefully i’m in the right category
Objective – setup a page / dashboard as follows
- User inputs a single date value (I imagine in a slicer but not fussy how) – e.g. 31st October.
- A Report on page shows a bar chart for the 7 days of sales from 25th – 31st October
There may be other items on the report that also show month to date numbers (e.g. cumulative sales for 1st – 15th October on the basis user selected 15th October) or KPI’s that show the latest weekend of sales vs the weekend before, or last N days total vs the N days before that or vs a target for that week (the important point being, the user input date acts as a MAX date to which all reports can reference)
- If the user had selected 30th October, the 7 day chart would show 24th – 31st October profile, and if selected the 27th October would show 20th-27th october… etc…
- In both of these reports, I would look to display the daily data, total for the week and comparison to previous weeks (last week or target data for this week)
- My data model has a DateTable (like Sam’s in videos), and Sales table with OrderID/Sales/Cogs/gross margin etc as columns
- I am assuming, that the solution somehow involves creating different measures of sales with different time filters - this works for me for example when i say for every date in the report context, show the last 7 days sales, but the reports still show all dates within the slicer (rather than last 7 days)
- for internal purposes its important that we can go back and filter on the view from say 2 days ago rather than say use the maximum date in the data.
Is there a way to achieve such a setup where the reports are basically dynamically filtered based on a single input of a date from the user. I’m trying to avoid using things like TODAY functions a
Many thanks for your help - look forward to being able to answer questions myself as I get better at power bi!