Dynamically date query

[yslee]

Hi. In my database, I have 2 columns (date and production) filter by a single date slicer.

I would like to dynamically plot the 7 days back production data based on the single date slicer value. How can I do this?

For example

Slicer date selected: 7th apr 2021

Production data retrieve

1st apr

2nd apr

3rd apr

4th apr

5th apr

6th apr

7th apr

[v-yetao1-msft · Accepted solution]

Hi @yslee ，

You can create a calendar table as a slicer to filter the data in Data table.

Calendar Date = CALENDAR(DATE(2021,04,01),DATE(2021,04,30))

Then create a measure to return the 7 days back production data.

last 7 days products = CALCULATE(SELECTEDVALUE('Table'[Product]),FILTER('Table','Table'[Date]>MAX('Calendar Date'[Date])-7 && 'Table'[Date]<=MAX('Calendar Date'[Date])))

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@yslee , if you select a date and plot more than that date , you need an independent date table https://www.youtube.com/watch?v=44fGGmg9fHI