Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Calculate with Filter

Hi there,    I have a sales data table as below. Each customer has more than 1 contract.   I want to calculate the total sales between the latest contract date for each customer and up to 31 Dec ...
calculation
DAX Calculation
DAX Commands and Tips
DAX Filtering
Help appreciated
Help me please
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  can you try this

    Measure =
CALCULATE (
    [Total Sales],
    FILTER (
        Sales,
        Sales[Contract Date]
            >= CALCULATE ( MAX ( Sales[Contract Date] ), ALLEXCEPT ( Sales, Sales[Customer] ) )
            && Sales[Contract Date] <= DATE ( 2021, 12, 31 )
    )
)

     

     

  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  try this

    Measure =
SUMX (
    VALUES ( 'Table'[Customer] ),
    CALCULATE (
        MAX ( 'Table'[Car Purchased] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer] )
    )
)

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous  can you try this

Measure =
CALCULATE (
    [Total Sales],
    FILTER (
        Sales,
        Sales[Contract Date]
            >= CALCULATE ( MAX ( Sales[Contract Date] ), ALLEXCEPT ( Sales, Sales[Customer] ) )
            && Sales[Contract Date] <= DATE ( 2021, 12, 31 )
    )
)

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi smpa01 

     

    Thank you for your advise. It works!!

     

    I tried to apply the same concept to a very simple scenario below but i couldn't seem to get the desired result. 

     

    I have Customer A and B purchased cars from me. Due to certain reason, Customer A signed 3 different contracts but the total cars purchased by Customer A was 200,000 (#A001+#A002+#A003). Customer B purchased 800 cars. Hence, in total, i have sold 200,800 cars.

     

    Excel:

     

    DAX:
    Total Cars Purchased =
    CALCULATE (max('Car Purchased'[Cars Purchased]), ALLEXCEPT('Car Purchased','Car Purchased'[Customer]))

     

    How should i amend the formula so that the total in Power BI will show 200,800 instead of 200,000? I am not sure what i did wrong here...

     

     

    Thank you!!

     

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymous  try this

      Measure =
SUMX (
    VALUES ( 'Table'[Customer] ),
    CALCULATE (
        MAX ( 'Table'[Car Purchased] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer] )
    )
)

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        hi smpa01 

         

        It works again!! i applied the logic to some other calculations in my dashboard and it all worked! 

         

        Thank you so much for your help!! You have just made the world a little better 😁

         

        Cheers!