topic Re: Find productions that reaches current inventory FIFO in DAX Commands and Tips

Find productions that reaches current inventory FIFO

yavuzonaran — Sun, 09 Jul 2023 08:57:23 GMT

Hello

I have to tables as below. I want to find production days of current inventory as we using fifo method and find inventory age with today() - production date. We assume today as 10.07.2023

Table 1-Current Inventory

Item1 100pcs

Item2 50pcs

Table 2-Productions

Item1 50pcs 01.07.2023

Item1 30pcs 03.07.2023

Item1 60pcs 07.07.2023

Item2 20pcs 02.07.2023

Item2 30pcs 04.07.2023

Item2 5pcs 08.07.2023

Expected result is;

Item1 60pcs 07.07.2023 3days

Item1 30pcs 03.07.2023 7days

Item1 10pcs 01.07.2023 9days

Item2 5pcs 08.07.2023 2days

Item2 30pcs 04.07.2023 6days

Item2 15pcs 02.07.2023 8days

Weighted average aging

Item1 100pcs 6,89days

Item2 50pcs 6,20days

Thanks in advance

Item	Quantity
Item1	100
Item2	50

Item	Quantity	Production Date
Item1	50	1.07.2023
Item1	30	3.07.2023
Item1	60	7.07.2023
Item2	20	2.07.2023
Item2	30	4.07.2023
Item2	5	8.07.2023

Re: Find productions that reaches current inventory FIFO

tamerj1 — Sun, 09 Jul 2023 10:58:02 GMT

Hi yavuzonaran
Not sure what exactly do you mean by weighted average.

Re: Find productions that reaches current inventory FIFO

yavuzonaran — Sun, 09 Jul 2023 12:04:56 GMT

Thanks for your reply

First i need to do below one. This is the aging detail of inventory in hand

Expected result is;

Item1 60pcs 07.07.2023 3days

Item1 30pcs 03.07.2023 7days

Item1 10pcs 01.07.2023 9days

Total 100pcs

Item2 5pcs 08.07.2023 2days

Item2 30pcs 04.07.2023 6days

Item2 15pcs 02.07.2023 8days

Total 50pcs

Re: Find productions that reaches current inventory FIFO

tamerj1 — Sun, 09 Jul 2023 13:15:05 GMT

yavuzonaran
Still not sure what are you trying to achieve

Re: Find productions that reaches current inventory FIFO

yavuzonaran — Sun, 09 Jul 2023 15:13:41 GMT

Thank you, i added as measure and it worked.

But i also need weighted average days. let me explain below, i hope this is clear to understand what i try to achieve

Item Qty Days

Item1 10 9

Item1 30 7

Item1 60 3

(10x9+30x7+60x3)/(10+30+60)=

480/100=4,80 weighted average days

Re: Find productions that reaches current inventory FIFO

tamerj1 — Sun, 09 Jul 2023 17:52:54 GMT

yavuzonaran
Please refer to attached sample file with the proposed solution

Quantity = VAR CurrentDate = MAX ( Productions[Date] ) VAR CumulativeQty = SUMX ( FILTER ( CALCULATETABLE ( Productions, ALL ( Productions ), VALUES ( 'Current Inventory'[Item] ) ), Productions[Date] >= CurrentDate ), Productions[Qty] ) VAR CurrentQty = SUM ( 'Current Inventory'[Qty] ) VAR ProducedQty = SUM ( Productions[Qty] ) RETURN IF ( NOT ISEMPTY ( Productions ), IF ( HASONEVALUE ( Productions[Date] ), IF ( CumulativeQty < CurrentQty, ProducedQty, CurrentQty + ProducedQty - CumulativeQty ), CurrentQty ) )

Average Days = IF ( HASONEVALUE ( Productions[Date] ), AVERAGEX ( Productions, DATEDIFF ( Productions[Date], TODAY ( ), DAY ) + 1 ), SUMX ( Productions, DIVIDE ( [Quantity] * ( DATEDIFF ( Productions[Date], TODAY ( ), DAY ) + 1 ), RELATED ( 'Current Inventory'[Qty] ) ) ) )

Re: Find productions that reaches current inventory FIFO

yavuzonaran — Sun, 09 Jul 2023 17:59:24 GMT

This is exactly what i try to do! Thanks a lot 🙏

Re: Find productions that reaches current inventory FIFO

yavuzonaran — Sun, 09 Jul 2023 19:27:55 GMT

Hello again,

I applied solution to my raw data, and there is a little problem need to be solved.

Problem

I think this expression causing this. I need a correction for turning negative

Re: Find productions that reaches current inventory FIFO

yavuzonaran — Sun, 09 Jul 2023 19:58:09 GMT

I also find out another problem like this below

because of edge value

produced 31.152 pcs at 27.05.2023 but current inventory is only 3.516 pcs.

its been only 43 days since produced but measure calculates

31.152/3.516x43=380,98

correct formula must calculate

3.516/3.516x43=43

Re: Find productions that reaches current inventory FIFO

yavuzonaran — Sun, 09 Jul 2023 21:23:40 GMT

https://file.io/A9a3bOpQv4Cl

updated file to show errors. could you please check?

Re: Find productions that reaches current inventory FIFO

tamerj1 — Mon, 10 Jul 2023 09:22:26 GMT

yavuzonaran
So negatives should be zero?

Re: Find productions that reaches current inventory FIFO

WinterMist — Mon, 10 Jul 2023 15:42:29 GMT

tamerj1

Wondering if you could educate me once again....this time, about the VAR [CumulativeQty].

In my mind:

- ALL (Productions) --REMOVES ALL FILTERS FROM TABLE 'Productions'

- VALUES ('Current Inventory'[Item]) --ADDS A FILTER FOR THE [Item] IN THE CURRENT FC.

IF a filter for the current item (e.g. Item1) is added, then the FC in the table visual for rows under Item1 should only relate to the 3 dates that apply to Item 1 in the table data:

- July 1

- July 3

- July 7

NOTE: The other 3 dates ( July 2, 4 & 8 ) apply to Item2 & should not appear in the Item1 FC.

However, if I return the VAR [CumulativeQty], the other 3 dates (for Item2) actually appear in the table visual within the Item1 FC (Filter Context).

This greatly confuses me.

I realize this does not impact your end result query as you specifically ignore where Productions ISEMPTY within your RETURN clause. So my q isn't really how you are handling it. My q is more of: Shouldn't your VALUES clause have handled it already???

I can override this by creating the VAR [ProductionsExist] (commented out in the screenshot above), but this is again the equivalent of what you are doing in your IF logic, and again seems like it should not be necessary.

Regards,

Nathan