Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

 

Spacewalker_1-1641982201462.png

 

Spacewalker_2-1641982222279.png

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?
Spacewalker_4-1641982441157.png

 

Thank you!!

 

 

 

 

 

2 ACCEPTED SOLUTIONS
smpa01
Super User
Super User

@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_0-1642000213333.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

@Anonymous  try this

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

 

smpa01_0-1642169641259.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

7 REPLIES 7
smpa01
Super User
Super User

@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_0-1642000213333.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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:

 

Spacewalker_2-1642153882532.png

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

 

Spacewalker_3-1642153938620.png

 

Thank you!!

 

@Anonymous  try this

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

 

smpa01_0-1642169641259.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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
Not applicable

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.

 

Spacewalker_0-1641996503318.png

 

Thank you!

 

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:

ValtteriN_0-1641997615218.png

 

Start data:
ValtteriN_1-1641997647912.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ValtteriN
Super User
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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors