Forum Discussion

Sofiane85's avatar
Sofiane85
Frequent Visitor
2 years ago
Solved

sales from different dates range

Hi all,
i have 2 tables, "Sales" and "Target"
in the Target table there is some of products and 2 dates columns : Start Date and End Date,
please, i need to calculate the sales from "Sales" table, only for dates that are between (>=  <=) the start date and end date, and only for the products that are in the Target Table (not all products of sales table)
the result needed in image

 

Target test 

Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    Sofiane85 

    Add following culumn to your target table:

    Sales = 
VAR __Prod = Target[Product] 
VAR __DateStart = Target[start date] 
VAR __DateEnd = Target[end date] 
VAR __Result = 
SUMX(
    FILTER(
        Sales,
        Sales[Product] = __Prod &&
        Sales[Date] >= __DateStart && Sales[Date] <= __DateEnd 
    ),
    Sales[Qte]
)
RETURN
    __Result

     

     

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Sofiane85 

    Add following culumn to your target table:

    Sales = 
VAR __Prod = Target[Product] 
VAR __DateStart = Target[start date] 
VAR __DateEnd = Target[end date] 
VAR __Result = 
SUMX(
    FILTER(
        Sales,
        Sales[Product] = __Prod &&
        Sales[Date] >= __DateStart && Sales[Date] <= __DateEnd 
    ),
    Sales[Qte]
)
RETURN
    __Result

     

     

    • Sofiane85's avatar
      Sofiane85
      Frequent Visitor
      2 years ago

      Fowmy  thank you so much;

      it works perfectely.
      please one more thing if you want, how i can visualize this by "Seller" (i dont have a relationship between the 2 tables)
      thank you again

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Sofiane85 

        The Seller column is in the Sales table, what is your question exactly?