Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Thank you!!
Solved! Go to 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 try this
Measure =
SUMX (
VALUES ( 'Table'[Customer] ),
CALCULATE (
MAX ( 'Table'[Car Purchased] ),
ALLEXCEPT ( 'Table', 'Table'[Customer] )
)
)
@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 )
)
)
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:
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!!
@Anonymous try this
Measure =
SUMX (
VALUES ( 'Table'[Customer] ),
CALCULATE (
MAX ( 'Table'[Car Purchased] ),
ALLEXCEPT ( 'Table', 'Table'[Customer] )
)
)
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!
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!
Hi,
@Anonymous
You can use this pattern to get the value for last sales date =
Start data:
Proud to be a Super User!
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!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |