Last 5 Week Trend

[yaman123]

Hi, 

I am looking for a query to show the last 5 weeks from the date selected in the slicer. I have a slicer which is Week Commencing (Mon - Sun) and I would like to present the data as below. If I select date 26/02/2024, the table should show as below. If i select date 19/02/2024, the latest date in the table should be 19/02/2024 and 22/01/2024 included and so on...

The main table and date table are not linked. 

TIA

	29/01/2024	05/02/2024	12/02/2024	19/02/2024	26/02/2024
Accidents	0	0	0	0	1
Absence	1	2	0	0	3
Safety Obs	1	1	1	1	0

[v-yaningy-msft · Accepted solution]

Hi, @yaman123 

Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

Data from five weeks ago =

IF (

    SELECTEDVALUE ( 'Table'[Date] )

        >= SELECTEDVALUE ( 'Date'[Date] ) - 35

        && SELECTEDVALUE ( 'Table'[Date] ) <= SELECTEDVALUE ( 'Date'[Date] ),

    1,

    IF ( NOT ISFILTERED ( 'Date'[Date] ), SELECTEDVALUE ( 'Table'[Date] ) )

)

IsMondayDate =

IF ( WEEKDAY ( 'Date'[Date_DateMeasure], 2 ) = 1, 1, 0 )


IsMondayTable =

IF ( WEEKDAY ( 'Table'[Table_DateMeasure], 2 ) = 1, 1, 0 )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum