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

     

     

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!