YTD with month filter using Direct Query

[anil]

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.

[Anonymous · Accepted 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.

[anil]

@Anonymous 

Thanks for the reply.

Previously I used allexcpet(), but it was not working. Now I tried again, able to see the expected result.

[Anonymous]

Cool, thanks for confirming.

Please mark my answer as accepted solution, will help others as well. 🙂