Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
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.
Solved! Go to Solution.
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]
)
)
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]
)
)
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 ()
)
)
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |