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 All,
I am trying to create YTD sales at category level with Month filter using power bi direct query.
If we select month filter value"March" the expected result should be the YTD sum till March.
But in direct query mode, I am unable to use YTD functions.
I have tried using this calculated measure but it's not giving YTD result at the
YTD = VAR lastDay = MAX ( A2[ORDER_RECEIVED_DATE_NEW] ) VAR year = YEAR ( lastDay ) RETURN CALCULATE ( SUM ( A2[Quantity] ), FILTER ( ALL ( A2 ), YEAR ( A2[ORDER_RECEIVED_DATE_NEW]) = year && A2[ORDER_RECEIVED_DATE_NEW] <= lastDay ) )
category level.
please help me with a solution.
Solved! Go to Solution.
Hi Anil
Can you please try using ALLEXCEPT instead of ALL since you are looking to see the data at the category level.
https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax
Best regards.
Hi Anil
Can you please try using ALLEXCEPT instead of ALL since you are looking to see the data at the category level.
https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax
Best regards.
@Anonymous
Thanks for the reply.
Previously I used allexcpet(), but it was not working. Now I tried again, able to see the expected result.
Cool, thanks for confirming.
Please mark my answer as accepted solution, will help others as well. 🙂
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 |