Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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 DateEntry TypeItem NoProduction DateLast Date(notes)
1104/28/19Sale103/01/1903/03/19refers to entry no 4
1004/28/19Sale103/01/1903/03/19refers to entry no 4
904/27/19Output104/27/19 no return, is not a sale
804/26/19Sale203/05/1903/12/19refers to entry no 7
704/25/19Sale203/12/19 no return, no previous sale
604/24/19Sale303/04/1902/27/19refers to entry no 2
504/23/19Consumption303/18/19 no return, is not a sale
404/22/19Sale103/03/19 no return, no previous sale
304/21/19Output204/21/19 no return, is not a sale
204/20/19Sale302/27/1902/26/19refers to entry no 1
104/19/19Sale302/26/19 no return, no previous sale
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        Hi Anonymous ,

         

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

         

        Regards,

        MFelix

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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,