calculate delivery and shipment date with conditions

[Anonymous]

Hi 🙂

I have a business problem i want to solve.

We produce a product in denmark where we have a compoent from poland.

We recieve our component on Tuesdays and Fridays. The same day components is shiped from poland we recieve it.

How ever we have special products there we need to recieve them 3 days or 7 days before on the closest tuesday or friday

if varenummer is 1600 or 1601 we need them 3 days before.

if varenummer is 1700 or 1701 we need them 7 days before

i have attaced a picture with the example where you can see that shipment days with empty varenummer is recieved the same day on either a tuesday or a friday. you also see that when it is 1600 or 1601 we have the delivery at least 3 days before and when it is 1700 or 1701 we recieve them at least 7 days before.

there are only TWO tabels. the date tabel and the sales table. sales table have shipment date but we need to calculate delivery date with the above information and conditions.

Thanks in advance

[Anonymous · Accepted solution]

hi

i solved the assignment in SQL instead of power bi.

thank a lot for your time 🙂

[v-rongtiep-msft]

Hi @Anonymous ,

Is column delivery date the output you want? Why when varenumber=1700, the column delivery date = 2022/2/4? And when varenumber=1700, the column delivery date = 2022/4/8?  ”When it is 1700 or 1701 we recieve them at least 7 days before.“ Is not?

A column.

column =
VAR threedaybefore = 'Table'[shipment date] - 4
VAR sevendayago = 'Table'[shipment date] - 8
RETURN
    IF (
        ISBLANK ( 'Table'[varenumber] ),
        'Table'[shipment date],
        IF (
            'Table'[varenumber] = 1600
                || 'Table'[varenumber] = 1601,
            threedaybefore,
            IF (
                'Table'[varenumber] = 1700
                    || 'Table'[varenumber] = 1701,
                sevendayago,
                BLANK ()
            )
        )
    )

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

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.