March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi there,
I am struggling to implement the above in my Power BI and have tried other similar solutions using CALCULATE / MINX / FILTER etc. I basically want to have it so that a date can be seen in my matrix visual that will appear for when a stock for the particular item will reach 0 (IF it does, IF NOT THEN it does not have to return a value.
My table is as follows:
TYPE | ITEM | STOCK | DATE |
A | Apple | 2000 | 07/11/22 |
A | Apple | 0 | 14/11/22 |
C | Banana | 500 | 28/11/22 |
C | Banana | 0 | 21/11/22 |
C | Banana | -222 | 28/11/22 |
A | Carrot | 1000 | 01/01/23 |
D | Nuts | -2421 | 01/03/24 |
D | Nuts | -5002 | 08/03/24 |
For example, this should hopefully show:
TYPE | ITEM | EARLIEST DATE |
A | Apple | 14/11/22 |
C | Banana | 21/11/22 |
D | Nuts | 01/03/24 |
Any ideas on how to achieve this either through DAX or a Measure?
Thanks in advance.
Solved! Go to Solution.
Hi @TotunG ,
Has your problem solved by @johnt75 's solution? If not, try this:
Measure:
Earliest date =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Type] = MAX ( 'Table'[TYPE] )
&& 'Table'[Item] = MAX ( 'Table'[ITEM] )
&& 'Table'[Stock] <= 0
)
)
Or calculated column:
Column =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[Type] = EARLIER ( 'Table'[TYPE] )
&& 'Table'[Item] = EARLIER ( 'Table'[ITEM] )
&& 'Table'[Stock] <= 0
)
)
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @TotunG ,
Has your problem solved by @johnt75 's solution? If not, try this:
Measure:
Earliest date =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Type] = MAX ( 'Table'[TYPE] )
&& 'Table'[Item] = MAX ( 'Table'[ITEM] )
&& 'Table'[Stock] <= 0
)
)
Or calculated column:
Column =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[Type] = EARLIER ( 'Table'[TYPE] )
&& 'Table'[Item] = EARLIER ( 'Table'[ITEM] )
&& 'Table'[Stock] <= 0
)
)
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try
Earliest date =
CALCULATE (
MIN ( 'Table'[Date] ),
ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Item] ),
'Table'[Stock] <= 0
)
Thanks for the quick reply - This as a Column made it say 01/01/2022 for all values but thank you for the suggestion.
I meant it as a measure. If you need it as a column it would need some tweaks.
Ah apologies - Still the same result unfortunately. Thank you though.
It works in my sample
Do you have any filters being applied to other tables? They may need to be removed using REMOVEFILTERS in the CALCULATE statement
Okay I see - Whereabouts exactly would I put REMOVEFILTERS? Can't see that there is any filters but you're right that it looks like its working for you.
Not sure if this will work but you can try
Earliest date =
CALCULATE (
MIN ( 'Table'[Date] ),
ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Item] ),
REMOVEFILTERS(),
'Table'[Stock] <= 0
)
Not sure how the REMOVEFILTERS will interact with the ALLEXCEPT. You may need to specify each table that is connected to your main table individually, one per line, each in their own REMOVEFILTERS call.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
85 | |
76 | |
49 |
User | Count |
---|---|
163 | |
148 | |
103 | |
74 | |
55 |