Include dates out of slider selection

[hendrikhendriks]

Hi All,

I am try to display the amount of open item at a certain point in time. An item is open when it has been Entered and has not yet been released. I found a way to do this, but my slicer selection tampers with my calculation. I'll explain in more detail below.

The following is an example of my Table:

Please note "-1" means the Item is not yet released.

Item	Entry Date	Release Date
1	30-12-2021	2-2-2022
2	30-12-2021	-1
3	1-3-2022	2-4-2022
4	2-3-2022	3-4-2022
5	11-4-2022	12-5-2022
6	11-4-2022	12-5-2022
7	4-5-2022	5-6-2022
8	4-5-2022	-1
9	1-6-2022	5-6-2022
10	1-6-2022	-1

I use the following DAX statement to show the amount of open items at a given point in time.

History =

var Entry = SELECTEDVALUE(Table[Entry_Date])

var SumTable = SUMMARIZE(filter(ALLSELECTED(Table), AND(Entry >= Table[Entry_Date], OR(Entry <= Table[Release_date], Table[Release_date] = -1))),

Table[ItemId],

Table[Entry_Date],

Table[Release_date])

return COUNTROWS(SumTable)

This works fine untill I use a slicer containing Entry Date. In the example below my Items with a Entry date before 2022 are not included. I want to included the Items with a Entry Date before 2022 if they haven't been released. On the other side I only want to show Dates in 2022 on my axis.

Can anyone help me with the DAX statement?

In January the amount of items is too low due to the slicer selection. The line should remain around the level of Feb and March

[hendrikhendriks]

Hi @v-easonf-msft ,

Thank you for your reply! I tried it immediately, but unfortunately it does not do what I wanted. The first few days after the MIN_date are still not what they are supposed to be. I need to find all items of which the entry date is before the entry date on the axis in the graph but are not yet released ( at least at the given date on the axis).

The DAX statement does achieve this, but neglects items with a entry date before the MIN_DATE of the slicer.

Do you know how I can include these as well?

[v-easonf-msft]

Hi， @hendrikhendriks 

I'm still a little confused about your needs.

Can you give an example to further illustrate your expected results?

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi, @hendrikhendriks 

For single date value you can use SELECTEDVALUE, but for date range you may need to use max/min.

Hop this could help:

History =
VAR _min =
    MIN ( Table[Entry_Date] )
VAR _max =
    MAX ( Table[Entry_Date] )
VAR SumTable =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( Table ),
            AND (
                _MIN >= Table[Entry_Date],
                OR ( _MAX <= Table[Release_date], Table[Release_date] = -1 )
            )
        ),
        Table[ItemId],
        Table[Entry_Date],
        Table[Release_date]
    )
RETURN
    COUNTROWS ( SumTable )

Best Regards,
Community Support Team _ Eason