Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Label each sales with different date ranges

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 ?

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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] )