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,
I have a simple table with Date, Product Name and Value. I need a measure that will provide a Total 'value' for the dates <= a date chosen by the user, of which the following works fine:
Measure = CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALL('0 - Measureables'),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))
HOWEVER, this completely ignores the 'Product Name' slicer (i assume because its referencng ALL. How can i adapt this so that the product name slicer is still filtered. Therefore giving a total for All previous dates for the chosen product name too.
Many Thanks
Stu
Solved! Go to Solution.
Base on my test, we can use the function ALLEXCEPT instead of ALL here to make the product name keeping filtered.
So we can create a measure using the formula as below:
ALLEX := CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALLEXCEPT('0 - Measureables','0 - Measureables'[Product]),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))
After that we can get the result that we need.
For more information, please refer to the pbix as attached.
Regards,
Lydia
Base on my test, we can use the function ALLEXCEPT instead of ALL here to make the product name keeping filtered.
So we can create a measure using the formula as below:
ALLEX := CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALLEXCEPT('0 - Measureables','0 - Measureables'[Product]),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))
After that we can get the result that we need.
For more information, please refer to the pbix as attached.
Regards,
Lydia
Brilliant Thank you.
If you simply want to display the total value for a specific set of dates, cant you use a date slicer together with a "measure = sum(measureables[value])?
Hi, thank you for the response. Unfortunately though i need to display sum of the values (total) for each measure but only until the date that the user selected, not all the dates in the table as some may be after this point. In the seperate column i have a measure showing exactly what you have suggested, but that only gives the total for the chosen day.
Hope this makes sense,
Thanks
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |