Configure first day of week for "relative date" filter on page or visual

[juergenroehr-rd]

Hi everyone,

I'm building a report, which is based on calendar weeks. I'd like to filter data on some visuals to the last n calendar weeks. Unfortunately it seems as if those weeks are as ranging from sunday to saturday - which interferes with the measures and date dimesnion I've created (as my week starts on monday, data from the last sunday is not considered as it should ... just a tiny error, but still an error).

Is there a way to configure the definition of a week for page/visual filters? Alternatively: Is there a way to programmatically define a custom filter on the page/visual?

Any help is appreciated.

Thanks in advance.

[PaulDBrown · Accepted solution]

This is a long standing bug which Microsoft chooses to ignore unfortunately....

Relative date slicer bug

[juergenroehr-rd]

Edit:

I have

1. a table with events consisting of a date and a description

2. a date dimension table (marked as date table) with a european calendar week calculated from "EuropeanCalendarWeek = WEEKNUM(DimDate[Date], 21)"

3. a relation between the two date columns

4. a measure counting the events

This is what I get as visuals

The topmost table shows the data and the linked european calendar week.

The "Unfiltered" matrix shows the calendar weeks and their respective count.

The "Filtered ..." is a copy with a filter applied to the visual.

I assume, that "Event #4" is not included, because the visual filter uses a calendar week which starts on Sunday an thus would sort "Event #4" into week 51 (which at the time of writing would be the current week).

So, I want to configure the start date of the visual filter as I can do it with the "WEEKNUM" function. Is that possible?

Alternatively: Can I programmatically apply a filter to a visual (or page), which calcualtes the european calendar week start end end dates based on the current date?

[v-shex-msft]

HI @juergenroehr-rd,

>>So, I want to configure the start date of the visual filter as I can do it with the "WEEKNUM" function. Is that possible?

Yes, this part is possible to achieve. You can use aggregate function to extract text selection from filter/slicer then you can use date function with these to calculate the correspond date values.

Row Context and Filter Context in DAX - SQLBI

BTW, current power bi does not support create dynamic calculated column/table based on filter effects. 

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @juergenroehr-rd ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[juergenroehr-rd]

Hi @v-shex-msft,

thank you for the reply. Unfortunately I get a "PBIX is not allowed" when trying to attach a demo file. So, I added a description which hopefully makes the case more understandable.

Regards

Jürgen Röhr