Label each sales with different date ranges

[Anonymous]

Hello

My business define each sales by "date_order" and "date_delivery".

Therefore, I have a Sales_Table.
Sales table
and a Label_Table
Label_Table

The aim is to label the Sales_Table. Here is the expected result.

I would like to avoid a huge work by creating an index tables (which would concatenate OrderDate and DeliveryDate) from 2018 to 2019 with all the possibilities on excel.

Have you got a dynamic solution please ?

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may refer to below measure and drag it for sales table.

Measure  =
CALCULATE (
    MAX ( Label[Label] ),
    FILTER (
        GENERATE ( Sales, Label ),
        Sales[Order date] >= Label[OrderDate_start]
            && Sales[Order date] <= Label[OrderDate_end]
            && Sales[Delivery date] >= Label[Delivery date_start]
            && Sales[Delivery date] <= Label[Delivery date_end]
    )
)

Regards,

Cherie

[Anonymous]

Thanks a lot @v-cherch-msft , your solution is perfect as a measure.
Have you got a similar formula to convert this measure in column ?
I tried to use addcolums without success 😞

[Anonymous]

I created a table inspired from your formula.

TableJoin = SELECTCOLUMNS(

FILTER(

CROSSJOIN(Label;'Order');

[Order date] >= Label[OrderDate _ start].[Date]

&& [Order date] <= Label[OrderDate_end].[Date]

&& [Delivery date] >= Label[DeliveryDate_start].[Date]

&& [Delivery date] <= Label[DeliveryDate_end].[Date]);

"OrderID"; [OrderID];"Label" ; Label[Label] )

[Greg_Deckler]

Can you post that data in text, I really don't want to type all of that.

[Anonymous]

@Greg_Deckler

Oh deeply sorry, here is the data

Label;OrderDate _ start;OrderDate_end;DeliveryDate_start;DeliveryDate_end
A;43101;43240;43105;43342
B;43241;43311;43282;43444
C;43312;43465;43313;43480
D;43435;43600;43481;43616

OrderID;Order Date;Delivery Date
1;43133;43250
2;43194;43251
3;43240;43342
4;43240;43344
5;43241;43282
6;43241;43245
7;43311;43444
8;43312;43444
9;43444;43474
10;43465;43480
11;43435;43481
12;43465;43481
13;43599;43616
14;43599;43617
15;43601;43603