Need help solving a practical FIFO issue, DAX measure or calculated column

[Anonymous]

Hello,

I have table that contains some sales data. I want to detect a product rotation issue. Effectively, I want each row to list the MAX production date of ENTRY TYPE SALE on a POSTING DATE that is earlier than the current row's posting date. Desired results in red with notes explaining why:

Entry No.	Posting Date	Entry Type	Item No	Production Date	Last Date	(notes)
11	04/28/19	Sale	1	03/01/19	03/03/19	refers to entry no 4
10	04/28/19	Sale	1	03/01/19	03/03/19	refers to entry no 4
9	04/27/19	Output	1	04/27/19		no return, is not a sale
8	04/26/19	Sale	2	03/05/19	03/12/19	refers to entry no 7
7	04/25/19	Sale	2	03/12/19		no return, no previous sale
6	04/24/19	Sale	3	03/04/19	02/27/19	refers to entry no 2
5	04/23/19	Consumption	3	03/18/19		no return, is not a sale
4	04/22/19	Sale	1	03/03/19		no return, no previous sale
3	04/21/19	Output	2	04/21/19		no return, is not a sale
2	04/20/19	Sale	3	02/27/19	02/26/19	refers to entry no 1
1	04/19/19	Sale	3	02/26/19		no return, no previous sale

[v-cherch-msft]

Hi @Anonymous 

You may use below calculated column:

Column =
VAR Last_Date_Calculation =
    CALCULATE (
        MAXX ( 'Table', 'Table'[Production Date] ),
        FILTER (
            ALL ( 'Table'[Posting Date] ),
            'Table'[Posting Date] < EARLIER ( 'Table'[Posting Date] )
        ),
        ALLEXCEPT ( 'Table', 'Table'[Item No], 'Table'[Entry Type] )
    )
RETURN
    IF (
        Last_Date_Calculation = BLANK ()
            || 'Table'[Entry Type] <> "Sale",
        BLANK (),
        Last_Date_Calculation
    )

Regards,

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following measure:

Last_date =
VAR Last_Date_Calculation =
    CALCULATE (
        MAXX ( 'Table'; 'Table'[Production Date] );
        FILTER (
            ALL ( 'Table'[Posting Date] );
            'Table'[Posting Date] < MAX ( 'Table'[Posting Date] )
        );
        ALLEXCEPT ( 'Table'; 'Table'[Item No]; 'Table'[Entry Type] )
    )
RETURN
    IF ( Last_Date_Calculation = BLANK (); ""; Last_Date_Calculation )

Regards,

MFelix

[Anonymous]

The works well as a measure, but is affected by a slicer. It does not perform within the context I was hoping. Is it possible to perform the same function using a calculated column, so I can simply pull it into a table row-by-row? In this case, it would compare against all of the other shipments of that item across the entire table's history.

[MFelix]

Hi @Anonymous ,

What is the context you were expecting? The measure can be change to overcome the slicer context.

Regards,

MFelix

[Anonymous]

@MFelix ,

I was able to modify my approach to make use of the formula you provided - thank you!

MCKery

[v-cherch-msft]

Hi @Anonymous 

Glad to hear you've solved it, please accept the helpful reply as solution, that way, other community members will easily find the solution when they get same issue.

Regards,