Slicer to filter a date and show values between a start- and end-date

[Huybs]

Hi,

I have a set of historic data about all the orders we have received and shipped.

I know from this data that an order has been a 'backorder' between a startdate and an enddate.

What I want to do is show the backorder value of the orders at a certain point in time. I am guessing that I need to have a unique table with dates (1 row for every day), and link this with the historic backorder data... But how?

For example

Order number	Start date backorder	End date backorder	Order value
A	19-Oct	25-Oct	€10
B	22-Oct	27-Oct	€20
C	24-Oct	30-Oct	€15

If I were to slice to see the backorder status for 20-Oct I would see a value of €10

If I were to slice to see the backorder status for 23-Oct I would see a value of €30 (order A + B)

And so on...

Thank you in advance for your help!

[v-diye-msft · Accepted solution]

Hi,

1. Add a calendar table as slicer:

Table 2 = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))

2. Add below measure:

Measure = CALCULATE(SUM('Table'[Order value]),FILTER('Table',[Start date backorder]<=MAX('Table 2'[Date])&&[End date backorder]>=MIN('Table 2'[Date])))