Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |