cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver I

Return Date If Stock &lt;= 0

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?

1 ACCEPTED SOLUTION
Community Support

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.

8 REPLIES 8
Community Support

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.

Super User

Try

``````Earliest date =
CALCULATE (
MIN ( 'Table'[Date] ),
ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Item] ),
'Table'[Stock] <= 0
)
``````
Resolver I

Thanks for the quick reply - This as a Column made it say 01/01/2022 for all values but thank you for the suggestion.

Super User

I meant it as a measure. If you need it as a column it would need some tweaks.

Resolver I

Ah apologies - Still the same result unfortunately. Thank you though.

Super User

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

Resolver I

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.

Super User

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.

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors