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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
webbm
Frequent Visitor

Return Earliest date when Dax measure value hits 0

Hi All,

 

I'm trying to return the earliest date where my Stock Forecast reaches Zero. I have had some success with the below code...

 

OOS Forecast (Trade) = 
CALCULATE (
    MIN ( dimDate[CalendarDate] ),
    FILTER (
        ALL ( dimDate ),
        [YA Stock Forecast (Trade)] < 0
            && dimDate[CalendarDate] > TODAY ()
    )
)

 

 
This returns the correct date when only one ProductId is in a table

webbm_0-1656087086548.png


However across a number of ProductId's it returns the earliest date for all not individually as I would like. Notice how 465510 has a different date in the table below. Nothing has change apart from the ProductId filter generating this table. 

webbm_1-1656087330212.png

My output table is referencing a "dimProduct" Table the relationship is on ProductKey to a dimSales and other tables.

 

[YA Stock Forecast (Trade)] is a DAX measure built from the code below, essentially [Stock] - [Forecast Sales] ...

 

YA Stock Forecast (Trade) = CALCULATE (
    'Product Stock'[Stock QTY] - [Consumption Forecast (Trade)]
        + SUM ( 'factProjectedVendorOrder'[PO QTY] ),
    dimDate[CalendarDate]
        > TODAY () - 1,
    FILTER (
        ALLSELECTED ( dimDate[CalendarDate] ),
        dimDate[CalendarDate] <= MAX ( dimDate[CalendarDate] )
    )
)

 


Due to the volume of data I would like to keep forecast calculations within a measure not create a seperate table. Any help from someone more knowledgeable than myself would be really appreciated! I feel like I'm close but maybe missing something obvious?

** EDIT**
If it helps this is the output for a table generated with dimDate[CalandarDate], Measures[YA Stock Forecast (Trade)] filtered on "ProductId is 465510" around the dates im interested in. 

webbm_0-1656088054008.png

 

1 ACCEPTED SOLUTION
webbm
Frequent Visitor

For anyone else who comes across this in the future...

I couldn't find a more elegant solution to the problem so I created a calculated table for all ProductId's and moved the measure into a calculated column. This allowed me to add a line filter on the measure to ensure it is only looking at the single ProductId then created a relationship with dimProduct to pull the dates through into the origonal table.

 

 

Product Stock Dates (Table) = 
SUMMARIZE (
    dimCyclingProducts,
    dimCyclingProducts[ProductId],
    dimCyclingProducts[ProductKey]
)
Out of Stock Date (Column) =
CALCULATE (
    CALCULATE (
        MIN ( dimDate[CalendarDate] ),
        FILTER (
            ALL ( dimDate ),
            [YA Stock Forecast (Trade)] < 0
                && dimDate[CalendarDate] > TODAY ()
        )
    ),
    FILTER (
        ALL ( dimCyclingProducts ),
        dimCyclingProducts[ProductKey] = 'Product OOS Dates'[ProductKey]
    )
)

 

 

View solution in original post

3 REPLIES 3
webbm
Frequent Visitor

For anyone else who comes across this in the future...

I couldn't find a more elegant solution to the problem so I created a calculated table for all ProductId's and moved the measure into a calculated column. This allowed me to add a line filter on the measure to ensure it is only looking at the single ProductId then created a relationship with dimProduct to pull the dates through into the origonal table.

 

 

Product Stock Dates (Table) = 
SUMMARIZE (
    dimCyclingProducts,
    dimCyclingProducts[ProductId],
    dimCyclingProducts[ProductKey]
)
Out of Stock Date (Column) =
CALCULATE (
    CALCULATE (
        MIN ( dimDate[CalendarDate] ),
        FILTER (
            ALL ( dimDate ),
            [YA Stock Forecast (Trade)] < 0
                && dimDate[CalendarDate] > TODAY ()
        )
    ),
    FILTER (
        ALL ( dimCyclingProducts ),
        dimCyclingProducts[ProductKey] = 'Product OOS Dates'[ProductKey]
    )
)

 

 

ribisht17
Super User
Super User

@webbm 

 

The reason is probably  making use of ALL there that will force the filter context to Table level , you need to avoid that I guess

 

OOS Forecast (Trade) = 
CALCULATE (
    MIN ( dimDate[CalendarDate] ),
    FILTER (
        ALL ( dimDate ),
        [YA Stock Forecast (Trade)] < 0
            && dimDate[CalendarDate] > TODAY ()
    )
)

 

Regards,

Ritesh

@ribisht17 Thanks for the response. I've removed All and it performing the same as before unfortunately.

OOS Forecast (Trade) 2 = 
CALCULATE (
    MIN ( dimDate[CalendarDate] ),
    FILTER (
        dimDate,
        [YA Stock Forecast (Trade)] < 0
            && dimDate[CalendarDate] > TODAY ()
    )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors