DAX Calculate with Filter

[Anonymous]

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!!

[smpa01 · Accepted solution]

@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 · Accepted solution]

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

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 · Accepted solution]

@Anonymous  try this

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

[Anonymous]

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!

[Anonymous]

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]

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:

[ValtteriN]

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!