Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I can't figure out what dax formula to use.
I have a dimension table ITEM and FACT table holding all inventory changes.
Let's assume ITEMs A, B, C, D, E
And the FACT's:
Date Item Quantity
1-1-2016 A 1
7-1-2016 B 2
13-1-2016 C 3
19-1-2016 A 4
25-1-2016 B -1
31-1-2016 C -1
6-2-2016 D 6
The calculated measure to create is ' number of items without stock' (sum quantity = 0). This must also be calculated backwards, for example on the 31st of january.
The right results are:
- no time filter: 1 (Item E)
- on 31st of janary: 2 (D and E)
Problem I'm encountering is that when you start filtering on date, it automatically filters the FACT's and thus also the ITEM, which make item E dissapear.
Thanks in advance,
Johan Vermeulen
Solved! Go to Solution.
Hi @Johan,
First, you need to create a temporary table and calendar table:
Table = UNION ( 'FACT', SELECTCOLUMNS ( 'Item', "Date", MIN ( 'FACT'[Date] ), "Item", 'Item'[Item], "Quantity", 0 ) )
DIMDATE = CALENDAR("2016-01-01","2016-12-31")
Use the date from Dimdate table as slicer.
To display corresponding Item whose stock is 0, use this measure:
Measure 3 = VAR VAL = SUMX ( FILTER ( 'Table', [DATE] <= MAX ( 'DIMDATE'[Date] ) ), 'Table'[Quantity] ) RETURN IF ( VAL = 0, 0, BLANK () )
To show the number of how many items has no stock, use this measure:
Measure4 = SUMX ( SUMMARIZE ( FILTER ( 'Table', 'Table'[Date] <= MAX ( DIMDATE[Date] ) ), 'Table'[Item], "sumQuantity", SUM ( 'Table'[Quantity] ) ), IF ( [SumQuantity] = 0, 1, 0 ) )
Thanks,
Yuliana Gu
Hi @Johan,
First, you need to create a temporary table and calendar table:
Table = UNION ( 'FACT', SELECTCOLUMNS ( 'Item', "Date", MIN ( 'FACT'[Date] ), "Item", 'Item'[Item], "Quantity", 0 ) )
DIMDATE = CALENDAR("2016-01-01","2016-12-31")
Use the date from Dimdate table as slicer.
To display corresponding Item whose stock is 0, use this measure:
Measure 3 = VAR VAL = SUMX ( FILTER ( 'Table', [DATE] <= MAX ( 'DIMDATE'[Date] ) ), 'Table'[Quantity] ) RETURN IF ( VAL = 0, 0, BLANK () )
To show the number of how many items has no stock, use this measure:
Measure4 = SUMX ( SUMMARIZE ( FILTER ( 'Table', 'Table'[Date] <= MAX ( DIMDATE[Date] ) ), 'Table'[Item], "sumQuantity", SUM ( 'Table'[Quantity] ) ), IF ( [SumQuantity] = 0, 1, 0 ) )
Thanks,
Yuliana Gu
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |