Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
Have been looking at some posts and cannot find the solution for this. I have a purchase fact table like below
If the UnitsInStock is 800, I need a DAX measure for calculation Qty_Stock like below?
By the way the UnitsInStock can differ to my selection of date. (see measure below)
Is there a possible solution for this?
Itemnumber Date QTY bought Qty_Stock
23 20-06-2019 600 600
23 18-05-2019 50 50
23 23-04-2019 1000 150
UnitsInStock:=
IF (
MIN ( 'DIM_Time'[DateValue] ) <= CALCULATE ( MAX ( 'Fact_Inventory'[PostingDate] ); ALL ( 'Fact_Inventory' ) );
CALCULATE (
SUM ( 'Fact_Inventory'[Quantity] );
FILTER (
ALL( 'DIM_Time'[DateValue]);
'DIM_Time'[DateValue] <= MAX ( 'DIM_Time'[DateValue] )
)
)
)
Please formulate your question in a very clear fashion so that everyone (not only you) can understand it. Then maybe someone will be able to help you.
Thanks.
Best
Darek
Okay - if I for example have chosen today(from DIM_time) the UnitsInStock measure may give me a figure of 800 pcs. in Stock.
I would like to split this figure into 3 purchases according to my fact Purchase table like the column at the right.
Is that possible?
Has anybody had this challenge and found a solution?
Okay I will try to elaborate.
Itemnumber Date QTY bought Qty_Stock
23 20-06-2019 600 600
23 18-05-2019 50 50
23 23-04-2019 1000 150
As you can see from my Purchase_table, I have an item (no. 23), which has been purchased 3 times.
The total QTY bought is 1650(1000+50+600).
Now in another fact table(which also include sale) I calculate units in stock on a certain date with the measure below.
Now if we imagine that 30-06-2019 there is 800 left of the total QTY bought(1650). According to FIFO this is 600 from 20-06-2019, 50 from 18-05-2019 and 150 from 23-04-2019(1000 was bought, 850 of this has been sold afterwards)
How can I make a measure which returns the following from my purchase tabel?
Itemnumber Date QTY bought Qty_Stock
23 20-06-2019 600 600
23 18-05-2019 50 50
23 23-04-2019 1000 150
UnitsInStock:=
IF (
MIN ( 'DIM_Time'[DateValue] ) <= CALCULATE ( MAX ( 'Fact_Inventory'[PostingDate] ); ALL ( 'Fact_Inventory' ) );
CALCULATE (
SUM ( 'Fact_Inventory'[Quantity] );
FILTER (
ALL( 'DIM_Time'[DateValue]);
'DIM_Time'[DateValue] <= MAX ( 'DIM_Time'[DateValue] )
)
)
)
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |