YTD calculation with month Filter - direct query connection

[anil]

Hi All,

            Our data set has a product category, trying to show product sales at YTD when month slicer is selected.

Suppose we select September month the product sales should show YTD sales from January to September. True for any selected month.

That I have achieved using the following DAX

YTD  Sales =

VAR lastDay =
    MAX ( A2[ORDER_RECEIVED_DATE] )
VAR year =
    YEAR ( lastDay )
RETURN
    CALCULATE (
        SUM ( A2[Sales] ),
        FILTER (
            ALLEXCEPT(A2 ,A2[Product] ),
            YEAR ( A2[ORDER_RECEIVED_DATE]) = year
                && A2[ORDER_RECEIVED_DATE] <= lastDay
        )
    )

 Using this we are able to see YTD sales for selected month slicer.

But when we select month suppose say September in the slicer selection, the products are filtered for the September month and only products present in the month are showing up in the view. But then I need to see all the products present in the data set not the products in the select slice.

Note I am using direct query connection, so I cannot use the full capability of DAX

As shown above, the data has three products, but march month has only one product a.

even if we select march month, the view should show all the products and its relevant YTD sales value.

Please help me with a solution.

Sample pbix file:  https://www.dropbox.com/s/4l19pn8qy8nf0hn/Sample%20YTD.pbix?dl=0

[Stachu]

if you use A2[ORDER_RECEIVED_DATE] in the visual this should work (also in Direct Query), if you have dedicated date table you should use it the syntax

Sales YTD = CALCULATE (
        SUM ( A2[Sales] ),
        FILTER (
            ALL(A2[ORDER_RECEIVED_DATE] ),
            YEAR ( A2[ORDER_RECEIVED_DATE]) = year
                && A2[ORDER_RECEIVED_DATE] <= lastDay
        ),
        ALL(A2[Product])
)

 EDIT I changed the syntax to show all the products, ALLEXCEPT remves all the filter context except for the one in specified column, so it's different behaviour

[anil]

@Stachu I have tried the above formula but still, the products are getting filtered when I select any month filter.

[Stachu]

if you have proper Calendar table, I'd suggest using DATESYTD in CALCULATE

https://msdn.microsoft.com/en-us/query-bi/dax/datesytd-function-dax

code would look like this

CALCULATE (
        SUM ( A2[Sales] ),
        DATESYTD ( Calendar[Date])
)

in this article
https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

in 'The Mark as Date Table Setting' it's explained how to setyour calendar table 

[anil]

@Stachu thanks for the reply.

        But I am using direct query connection.

Also, my requirement is even if we filter month it should show all the products without filtering products.