Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TotunG
Resolver I
Resolver I

Return Date If Stock <= 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:

 

TYPEITEMSTOCKDATE
AApple200007/11/22
AApple014/11/22
CBanana50028/11/22
CBanana 021/11/22
CBanana-22228/11/22
ACarrot1000

01/01/23

DNuts-2421

01/03/24

Nuts-5002

08/03/24

 

For example, this should hopefully show:

TYPEITEMEARLIEST DATE
AApple14/11/22
CBanana21/11/22
DNuts01/03/24

 

Any ideas on how to achieve this either through DAX or a Measure?

 

Thanks in advance.

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
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.

View solution in original post

8 REPLIES 8
v-yanjiang-msft
Community Support
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.

johnt75
Super User
Super User

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

johnt75_0-1667922320327.png

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.