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

     

     

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: