Display last 5 weeks based on Selected Date
Hi everyone, I am pretty new to Power Bi and DAX, so I'd appreciate any pointers here. Here is my problem: Based on the selected date, I need to display data in a matrix table on the selected date and 5 weeks ago. Unfortunately, this is a problem from work, so I'm unable to share the actual data but here is an example with some dummy data: My selected date is 9/15/23. This is the data that should be displayed: If I select the date to be 9/8/23, this is how the new selected data should be displayed: In essence, selecting 9/8/23 results in the 9/8/23 totals to be added to the 9/15/23 totals. The remaining data results would display weekly from 8/11/23 up to 9/1/23. This type of functionality should exist for any selected date I choose. Here is a dummy view of what my actual table looks like. I also have a independent date table that is the distinct updated date from my actual table. Updated Date Days Open Status Count Date Difference (Today - Updated Date) 9/15/23 0 to 9 A 3 6 9/15/23 10 to 19 A 13 6 9/15/23 0 to 9 B 5 6 9/15/23 10 to 19 B 10 6 9/8/23 0 to 9 A 5 13 9/8/23 10 to 19 A 3 13 9/8/23 0 to 9 B 6 13 9/8/23 10 to 19 B 5 13 Currently, I have a display similar to the 1st picture, but this is without selecting any dates. I calculated a date difference between the updated date and Today's date and used those numbers as a filter to help display something similar to the first picture. Now if I try to select a date using a filter, it will only display that selected date's data, which isn't what I want. I tried to utilize the independent date table in the filter but obviously was running into issues because there's no actual relationship between my actual table and the date table. And even if I'm able to figure out this issue, I'm not even sure how I can visualize the second picture. Is this type of visualization even possible in Power BI? I just don't know where to begin 😞
