Previous Week(s) Slicers

[ergocorp]

Hi all,

Got a bit of a problem here that I'm wondering if it can be solved. There's plenty of literature around the internet on this subject, but none of them seem to apply to my situation.

I have multiple workbooks that tell me if a supplier invoice was paid to agreed terms. These are dated by wht day they were paid. I do a report every week though, so I've done some work on my date table to highlight this:

_week = WEEKNUM('Date Table'[Date]+1,21)

_week2 = FORMAT('Date Table'[_week],"0#")

Financial Week = CONCATENATE('Date Table'[_year],'Date Table'[_week2])

A slicer on that works fine for all my data.

I'm trying to reproduce (formatting is a bit different) the Exel workbook that is produced ech week, and I've hit a bit of a wall with a page that shows all the KPIs for current week, last week (week-1), the week before that (week-2) and the week before that (week-3).

Here's what that looks like:

I create a copy of the previous week's workbook and update the tabs I have with data from the new week. The way this tab works is literally just copy-paste the data along one table, and manually fill in the current week's scores from a pivot. However, Power BI can automate a lot and we clearly can do better than that!

The idea I had was if the data is filtered by week, then I just create 3 extra slicers, one each for a week less Power BI Slicers don't dynamically update the selection dpending on other slicers, but Chiclet Slicers seem to. This has led to:

These values are the following columns:

Financial Week -1 = IF(OR(AND(VALUE(RIGHT('Date Table'[Financial Week]-1,2))>52,VALUE(RIGHT('Date Table'[Financial Week]-1,2))<=99),VALUE(RIGHT('Date Table'[Financial Week]-1,2))=0),VALUE(CONCATENATE(LEFT('Date Table'[Financial Week],4)-1,"52")),value('Date Table'[Financial Week]-1))

Financial Week -2 = IF(AND(VALUE(RIGHT('Date Table'[Financial Week -1]-1,2))>-1,VALUE(RIGHT('Date Table'[Financial Week -1]-1,2))<1),VALUE(CONCATENATE(LEFT('Date Table'[Financial Week -1],4)-1,"52")),VALUE('Date Table'[Financial Week -1]-1))

Financial Week -3 = IF(AND(VALUE(RIGHT('Date Table'[Financial Week -2]-1,2))>-1,VALUE(RIGHT('Date Table'[Financial Week -2]-1,2))<1),VALUE(CONCATENATE(LEFT('Date Table'[Financial Week -2],4)-1,"52")),VALUE('Date Table'[Financial Week -2]-1))

I understand these aren't the most elegant formulae, but they currently work, with the key part being if the -1 would take the week below 01 (I also realise that some years have 53 weeks, so that "52" will need to be function, but they currently are not the problem).

The problem it appears, is that they only seem like the outputted value. They all slicer the data the same as the unaltered Week Number Slicer:

The cards are only affected by the slicer above them. I'm speculating this seem bit I mentioned is because they are on the same row:

Any ideas how I'd resolve this?