DAX Calculated Column for Last Delivery Date

Power-CJ · ‎02-23-2023

Hello,

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

Sell-to-Customer	Shipment Day	Last Shipment Day
52555	13.01.2023
52555	03.02.2023	13.01.2023
52555	03.02.2023	13.01.2023
52555	08.02.2023	03.02.2023
52555	10.02.2023	08.02.2023
52555	17.02.2023	10.02.2023
52555	22.02.2023	17.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])

)

Power-CJ · ‎02-27-2023

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 · ‎02-27-2023

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 · ‎02-27-2023

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

Anonymous · ‎02-27-2023

Hi @Power-CJ ,

I am very sorry, I have modified my program, please try again.

Column =
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index] < EARLIER ( 'Table'[Index] )
                && 'Table'[Sell-to-Customer] = EARLIER ( 'Table'[Sell-to-Customer] )
        )
    )
VAR _2count =
    CALCULATE (
        COUNT ( 'Table'[Shipment Day] ),
        FILTER (
            'Table',
            'Table'[Shipment Day] = EARLIER ( 'Table'[Shipment Day] )
                && 'Table'[Sell-to-Customer] = EARLIER ( 'Table'[Sell-to-Customer] )
        )
    )
VAR _shang2 =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            'Table',
            _2count > 1
                && 'Table'[Sell-to-Customer] = EARLIER ( 'Table'[Sell-to-Customer] )
                && 'Table'[Shipment Day] = EARLIER ( 'Table'[Shipment Day] )
        )
    )
VAR _nextday =
    CALCULATE (
        MAX ( 'Table'[Shipment Day] ),
        FILTER (
            'Table',
            'Table'[Sell-to-Customer] = EARLIER ( 'Table'[Sell-to-Customer] )
                && 'Table'[Index] = _1
        )
    )
RETURN
    IF (
        _2count > 1
            && 'Table'[Index] = _shang2,
        _nextday,
        IF ( _2count > 1 && 'Table'[Index] <> _shang2, BLANK (), _nextday )
    )

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 · ‎03-02-2023

Hello,

no problem - thanks a lot for the optimizing of the code.

I have problems to implement the DAX-Code in a calculated Column.

After some time PBI break up, becaus of limited capacity.

My Table in the Dataset has about 150.000 rows.

I think the DAX Code is to complex for big Tables.

Anonymous · ‎02-26-2023

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.