In Stock by Date

[Anonymous]

Hi,

I am trying to calculate in stock rates as a measure and am having trouble writing the DAX.

I have item, store, date, inventory, and approved.

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.

Any help would be appreciated.

[v-yulgu-msft]

Hi @Anonymous,

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] <> BLANK () )
)
    / CALCULATE (
        COUNT ( Table[stores] ),
        FILTER ( ALLSELECTED ( Table ), Table[approved] = "yes" )
    )

For more advice, please provide sample data and desired result. How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[Anonymous]

Thank you @v-yulgu-msft for your help!

That's definitely progress!

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!!

Measure = CALCULATE ( COUNT ( Table[Store] ), FILTER ( ALLSELECTED ( Table ), Table[Approved] = TRUE() ), FILTER ( ALLSELECTED ( Table ), Table[Inventory] > 0 ) ) / CALCULATE ( COUNT ( Table[Store] ), FILTER ( ALLSELECTED ( Table ), Table[Approved] = TRUE() ) )

I have dummy data I can attach as well. Here is a snapshot.

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