In Stock by Date

Question

Hi,&nbsp;I am trying to calculate in stock rates as a measure and am having trouble writing the DAX.&nbsp;I have item, store, date, inventory, and approved.&nbsp;I would like to have a measure that has (# of stores with inventory/# of stores approved) by item and by day. Ideally we'd then be able to slice by higher item-category levels or by month to date etc.&nbsp;&nbsp;Any help would be appreciated.

v-yulgu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
Add fields [item] and [date] (set its type to hierarchy and remove all levels expect for day) into visual. The measure could be similar to:
Measure =
CALCULATE (
    COUNT ( Table[stores] ),
    FILTER ( ALLSELECTED ( Table ), Table[inventory] &lt;&gt; BLANK () )
)
    / CALCULATE (
        COUNT ( Table[stores] ),
        FILTER ( ALLSELECTED ( Table ), Table[approved] = "yes" )
    )
For more advice, please provide sample data and desired result.&nbsp;How to Get Your Question Answered Quickly
&nbsp;
Best regards,
Yuliana Gu

anonymous · Answer

Thank you&nbsp;v-yulgu-msft&nbsp;for your help!&nbsp;That's definitely progress!&nbsp;I ended up doing the following, which gets my desired result, but I am unable to do visualization filtering based on items/hierarchy. In addition I have a store hierarchy that I would filter by (maps, etc.) The percent is static so just need a little bit more help!!&nbsp;Measure = CALCULATE ( COUNT ( Table[Store] ), FILTER ( ALLSELECTED ( Table ), Table[Approved] = TRUE() ), FILTER ( ALLSELECTED ( Table ), Table[Inventory] &gt; 0 ) ) / CALCULATE ( COUNT ( Table[Store] ), FILTER ( ALLSELECTED ( Table ), Table[Approved] = TRUE() ) )&nbsp;&nbsp;I have dummy data I can attach as well. Here is a snapshot.&nbsp;DateStoreItemInventoryApprovedItem Level 1Item Level 2Item Level 3Item Level 49/16/20185088brussels sprouts10II4I453I453VV9/16/20182839cabbage140SS2S200S200BW9/16/20183475celery80TT2T2X3T2X3YC9/20/20184323eggplant1690SS4S400S400BA9/20/20187188leeks120II9I933I933MI9/20/20184516lettuce60II1I108I108F19/20/20182682mushrooms50II9I943I943MT9/20/20184104radishes130SS2S219S219BN9/20/20183454squash161SS6S601S601D29/20/20185600swiss chard50SS2S208S208C29/20/20183277turnips120II7I753I753LG9/20/20183764zucchini60II9I933I933MI&nbsp;&nbsp;

Forum Discussion

In Stock by Date

2 Replies

Related content

Inventory and Stock balance calculation

stock ageing

stock ageing

Baseline Stock

Dax Queries for Open Close stock

Date	Store	Item	Inventory	Approved	Item Level 1	Item Level 2	Item Level 3	Item Level 4
9/16/2018	5088	brussels sprouts	1	0	I	I4	I453	I453VV
9/16/2018	2839	cabbage	14	0	S	S2	S200	S200BW
9/16/2018	3475	celery	8	0	T	T2	T2X3	T2X3YC
9/20/2018	4323	eggplant	169	0	S	S4	S400	S400BA
9/20/2018	7188	leeks	12	0	I	I9	I933	I933MI
9/20/2018	4516	lettuce	6	0	I	I1	I108	I108F1
9/20/2018	2682	mushrooms	5	0	I	I9	I943	I943MT
9/20/2018	4104	radishes	13	0	S	S2	S219	S219BN
9/20/2018	3454	squash	16	1	S	S6	S601	S601D2
9/20/2018	5600	swiss chard	5	0	S	S2	S208	S208C2
9/20/2018	3277	turnips	12	0	I	I7	I753	I753LG
9/20/2018	3764	zucchini	6	0	I	I9	I933	I933MI