Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |