Setting up Time dimension effectively for previous N Days display

Power BI Data Model
1

Hi everyone

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)

Notes

  • 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!

James

2

Check out this particular tutorial here

There’s actually a bit you need to do and understand to get this working.

It all comes down to understanding the context of the calculation. When you select the date column from the date table it places the specific context over your entire report page. That’s why this can be difficult to solve.

Check out the solution though and you’ll see that this can be done with some unique setup in your data model.

I think you’ll like the functionality of this once you can get it into your report.

Chrs

3

Perfect solution! thanks! this has opened up lots of interesting avenues

appreciate the quick response!

James

4

That’s great.