Slicing date interval on weeks

[Anonymous]

Hello everyone.

I have a problem filtering date interval on weeks when the date interval is more than 1 week.

I have multiple activities trough out the year. I want to be able to select a specific week and see what activies have dates in that week. Even if its just 1 day (Start or end date).

Following is pictures of my problem.

I've set up two sliders to select year (år) and week (uge).

When week 7 is choosen I see the below activity, which starts 08-02-2023 ( Wednesday, week 6) and ends 01-03-2023 (Wednesday, week 9).

When I slice week 8 (or 6 and 9) on the slider it doesent show up:

Can anyone help with this issue?

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a flag measure.

flag = 
IF(MAX('Table'[start date])<MAX('Date'[Date])&&MAX('Table'[end date])>=MIN('Date'[Date]),1)

Then apply it into filter.

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey @v-yalanwu-msft 

Thanks for the answer!

Is there a way to make this work with a relation to the time table? It only works if I remove the relation, but that will ruin other visuals.

[Anonymous · Accepted solution]

I fixed it by turning every date in the interval from start to end into a new line in power query.

Following link provides a quide to do so.

https://apexinsights.net/blog/convert-date-range-to-list

[Anonymous]

@amitchandak - Do you have a solution for this? 😉