Forum Discussion

Power-CJ's avatar
Power-CJ
Helper I
3 years ago

DAX Calculated Column for Last Delivery Date

Hello, 

 

I'm sure, that the solution is very simple, but i'm not getting in better mode for a while:

 

Sell-to-CustomerShipment DayLast Shipment Day
5255513.01.2023 
5255503.02.202313.01.2023
5255503.02.202313.01.2023
5255508.02.202303.02.2023
5255510.02.202308.02.2023
5255517.02.202310.02.2023
5255522.02.202317.02.2023

 

Here is my Table with the calculated Column Last Shipment Day. Because of 2 Orders at 03.02 there are double Values in my Calculated Column. This i want to avoid, because based on the LastDeliveryDate i'm calculating the Duration between Orders.

 

How i can proofe in my Dax Code if there have been calculatet the same date-value before.

 

Here is my Dax-Code of the Calculated Column Last Shipment Day:

 

Last Shipment Day =
CALCULATE(
    MAX('Order'[Shipment Date]),
    FILTER(
        'Order',
        'Order'[Sell-to Customer No_] = EARLIER('Order'[Sell-to Customer No_])
        && 'Order'[Shipment Date] < EARLIER('Order'[Shipment Date])
    )
)
Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Power-CJ ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a measure.

    last shipment da1y =
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Shipment Day] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sell-to-Customer] = SELECTEDVALUE ( 'Table'[Sell-to-Customer] )
                && 'Table'[Index]
                    = SELECTEDVALUE ( 'Table'[Index] ) - 1
        )
    )
VAR _2 =
    CALCULATE (
        COUNT ( 'Table'[Last Shipment Day] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sell-to-Customer] = SELECTEDVALUE ( 'Table'[Sell-to-Customer] )
                && 'Table'[Shipment Day] = SELECTEDVALUE ( 'Table'[Shipment Day] )
        )
    )
RETURN
    IF (
        _1 = BLANK (),
        BLANK (),
        IF ( _2 = 1, _1, MINX ( ALL ( 'Table' ), [Measure] ) )
    )

    Or a column.

    last shipment da1y col = 
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Shipment Day] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sell-to-Customer] = EARLIER(  'Table'[Sell-to-Customer] )
                && 'Table'[Index]
                    = EARLIER( 'Table'[Index] ) - 1
        )
    )
VAR _2 =
    CALCULATE (
        COUNT ( 'Table'[Last Shipment Day] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sell-to-Customer] = EARLIER(  'Table'[Sell-to-Customer] )
                && 'Table'[Shipment Day] = EARLIER( 'Table'[Shipment Day] )
        )
    )
RETURN
    IF (
        _1 = BLANK (),
        BLANK (),
        IF ( _2 = 1, _1, MINX (  ( 'Table' ), [Measure] ) )
    )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Power-CJ's avatar
    Power-CJ
    Helper I
    3 years ago

    Hello,

    thanks for the response. But this solution doesnt fit the requirement.

    In My Case the third line of Last ShipmentDay should be blank because the value 01/13/23 appears in the column before. If there will be a identical value in the column it should show blank.

     

    Based on this column i'm plannung to calculte an additional Column with the duration between delivery. If there wont be a blank, it will calculate this duration double times.

     

    I hope with this detail information u habe a better imagine of my issue.

     

    Thanks a Lot

     

    Power-CJ

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Power-CJ ,

      Have a try.

      Column = 
VAR _customer = 'Table'[Sell-to-Customer]
VAR _index = 'Table'[Index]
VAR _date_1 =
    CALCULATE (
        MIN ( 'Table'[Shipment Day] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sell-to-Customer] = _customer
                && 'Table'[Index] = _index - 1
        )
    )
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Last Shipment Day] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sell-to-Customer] = EARLIER ( 'Table'[Sell-to-Customer] )
                && 'Table'[Shipment Day] = EARLIER ( 'Table'[Shipment Day] )
        )
    )
VAR _index_doub =
    MAXX ( 
        FILTER ( 
            ALL('Table'),
            'Table'[Sell-to-Customer] = EARLIER ( 'Table'[Sell-to-Customer] )
                && 'Table'[Shipment Day] = EARLIER ( 'Table'[Shipment Day] )
                &&_count > 1
        ),
    'Table'[Index] 
    )
VAR _result =
    IF ( _index_doub <> _index, _date_1 )
RETURN
    _result

       

       

      How to Get Your Question Answered Quickly 

       

      If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

       

      Best Regards
      Community Support Team _ Polly

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Power-CJ's avatar
        Power-CJ
        Helper I
        3 years ago

        Thanks for the PBIX-File

        At the beginning it seems right, but was not working in my Dataset after implementing.

        I used your PBIX-File and added few new rows with new customer_No.

        I attched the picture with additional Data to check why calculation is breaking after new customer entry