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 2021. The result i am expecting is the yellow cells below and the total should be $1490 (1300+100+90).

 

 

Dax:

1. Latest Contract Start Date = max(Sales[Contract Date])
2. Total Sales from Latest Contract Start to end of Dec 2021 = CALCULATE([Total Sales],DATESBETWEEN('Calendar'[Date],[Latest Contract Start Date], date (2021,12,31)))
 
Below is the PowerBI result. at customer level, it is showing correct calculation. However, at total level, the $90 total sales is wrong as it is picking up the latest contract start date from the full list. How should i change the formula so that at total level, it will show as $1490?

 

Thank you!!

 

 

 

 

 

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

     

     

7 Replies

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

    Hi,

    Total calculation uses the same logic as the formula in the column it is in. You can create an IF logic to change this. E.g. Example = IF(ISBLANK(SELECTEDVALUE('Table'[Customer])),[Total sales],
    CALCULATE([Total Sales],DATESBETWEEN('Calendar'[Date],[Latest Contract Start Date], date (2021,12,31)))
    )

    The point here is to change the calculation logic when we are calculating  total.


    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!


  • 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 ValtteriN 

     

    Thanks for your advise.

     

    However, the formula will still arrive at wrong amount.

     

    The total amount should be the sum of all the lines above, which is total of $1490 (1300+100+90). The total should calculate each of the customers' sales at their respective latest contract start date.

     

     

    Thank you!

     

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

      Hi,
      Anonymous 
      You can use this pattern to get the value for last sales date = 

      LastDaySales =
      var _date = MAX('Calendar'[Date])
      Var _lastSalesDate = CALCULATE(max(Sales[Date]),all('Calendar'),Sales[Date]<=_date) return

      sumx(Sales,CALCULATE(SUM(Sales[Amount]),Sales[Date]=_lastSalesDate))


      This should get the desired end result:

       

      Start data: