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-Customer Shipment Day Last Shipment Day 52555 13.01.2023   52555 03.0...
Need Help
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Power-CJ
        Helper I
        3 years ago

        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.