cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hendrikhendriks
Frequent Visitor

Include dates out of slider selection

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.

ItemEntry DateRelease Date
130-12-20212-2-2022
230-12-2021-1
31-3-20222-4-2022
42-3-20223-4-2022
511-4-202212-5-2022
611-4-202212-5-2022
74-5-20225-6-2022
84-5-2022-1
91-6-20225-6-2022
101-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?

hendrikhendriks_0-1663163425157.png

 

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

 
3 REPLIES 3
hendrikhendriks
Frequent Visitor

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?

 

 

 

 

 

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
Community Support
Community Support

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

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors