Forum Discussion
Last 5 Week Trend
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 |
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 TeamIf 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
1 Reply
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 TeamIf 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