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.
Good Afternoon,
I am struggling to create a Custom Measure that will calculate the sum of a column while filtering on the Maximum Year Number in a difference column within the same table.
I am currently using the following DAX Expression:
Solved! Go to Solution.
Hello @BH22One
Give this a try. It uses ALL ( Product ) to clear the filter context then calc the max year in a variable. Then it uses the variable in the calc. The KEEPFILTERS is to stop the amount from appearing in the other years.
Max Year Sales = VAR MaxProductYear = CALCULATE ( MAX ( 'Product'[Year] ) , ALL ( 'Product' ) ) RETURN CALCULATE( SUM('Product'[Units]), KEEPFILTERS( 'Product'[Year] = MaxProductYear ) )
Hello @BH22One
Give this a try. It uses ALL ( Product ) to clear the filter context then calc the max year in a variable. Then it uses the variable in the calc. The KEEPFILTERS is to stop the amount from appearing in the other years.
Max Year Sales = VAR MaxProductYear = CALCULATE ( MAX ( 'Product'[Year] ) , ALL ( 'Product' ) ) RETURN CALCULATE( SUM('Product'[Units]), KEEPFILTERS( 'Product'[Year] = MaxProductYear ) )
Hi @jdbuchanan71 ,
Your solution worked perfect! I was also able to tweak it just a tad so that it changes in case I want to select a previous year in a slicer. I used ALLEXCEPT so that the variable would recognize a new max year in case I select a different year in the slicer.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |