Charts must show up only previous week data depending on the Max date selected in Date Slicer.

[Jyothishree]

Hello, I am looking for a solution where the data display on chart must show up only the data Previous week data depending on the user selection on Date slicer. For example if the user selects the Date Range (28.08.23 to 13.08.24), the chart must display the the data from 05.08.24 to 11.08.24 and if the user select a different date range say 01.01.24 to 31.01.24 then depending on the Max date(31.01.24), the charts must display (22.01.24 to 28.01.24). I created a weekoffset column and applied weekoffset is -1 in filterpane for the chart visual. This works for the default max date (Today)  but when the user selects a different date range then the chart goes blank. Also, I created 'IsInLastWeek' column in date table using dax and applied it on chart visual and selected 1.

IsInLastWeek =

IF(

    DateTable[isInCurrentYear] && DateTable[Week] = WEEKNUM(TODAY()) - 1,

    1,

    0

) but this also works similar to Week Offset, which does not satisfy the dynamic date selection in date slicer.

Any help is appreciated!

[v-bofeng-msft · Accepted solution]

Hi @Jyothishree ,

Let me explain why your data doesn't change dynamically based on the slicer.

The key is that the value of a calculated column is static and is not affected by slicers or other visuals, I suggest you using measure instead. I’ve made a test for your reference:

1\I assume there is a table named Tabelle1

2\Create a calculate table

CalendarTable = CALENDAR(date(2024,1,1),date(2024,12,31))

3\Create a measure for Tabelle1

IsInLastWeek = If(WEEKNUM(Max(Tabelle1[Date]))=Weeknum(Max(CalendarTable[Date]))-1,1,0)

4\Add a slider

5\Filter data

Best Regards,

Bof

[Jyothishree]

Thank you @v-bofeng-msft 

I am not able to access the PBI file you have shared. As it comes in Zip folder and when I extract, it says This file is restricted.

[v-bofeng-msft]

Hi @Jyothishree ,

I've reuploaded the attachment. Kindly take a moment to review it.

Best Regards,

Bof