Relative Date Weekly Filter

Power BI
1

Hi

Every Monday I send a report to a supplier that shows what is happening with pledges that were imported into our database 7 weeks earlier (calendar week).

For example, the report due on Monday, Jan 22nd will be for those pledges imported between 27/11/2023 and 03/12/2023 (Pledge Import Date):

image
image763×242 8.82 KB

I would like to automate the report - that is not have to manually change a date filter slicer every Monday.

The easiest option would be to add a report date filter where CurrWeekOffset = -7.

However, in my model there is no Date filter to the Recurrence table. Also, how do I get the CurrWeekOffset to filter the ‘Pledge Import Date’ data?

Or is there an easier way?

Thanks

Relative Weekly Date Sample.pbix (130.0 KB)
sample-recurrences.csv (38.1 KB)
sample-sales.csv (17.3 KB)

2

Hi @KieftyKids,

Just wondering if you are using the EDNA Date Table that @Melissa created for the forum? Maybe, if you can use that Date Table with the combination of offset already in the table automatically

Just a thought.
Keith

3

Hi Keith

Thanks for replying.

My model is using the extended date table. I’ve removed those columns not used in my model.

4

Hi @KieftyKids

I remember seeing offset columns values within that extended date table. I remember seeing the information in a video that Brian or Melissa was instructing.

You can always add something within that extended date table to make it easier.

Just a thought

Keith

5

Hi Keith

The problem I have is the filter doesn’t work. I think its because the Date table isn’t connected to the Recurrence table with the Pledge Import Date field is.

image

image
image853×725 26.9 KB

6

Did you try connecting the date field (Dates) table with the Recurrence import Date (Recurrences table)?

I’m also wondering if you change the recurrence date slicer to the Date table instead. (maybe I’m off base here).

Anything I do with Dates (filter/slicers), I’m always using the Date table and referencing to the fact tables.

Maybe i’m way off base here