YTD calculation with month slicer - 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 it 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

Thank You,

[anil]

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

[v-danhe-msft]

Hi @anil,

Could you please post your desired result if possible?

Regards,

Daniel He

[anil]

@v-danhe-msft Thanks for the reply,

If we select March slicer. The result should display for all the products:

Products  YTD Sales

a              2702352

b             3781456

c              2402070

The month slicer should not filter products, even though b and c products are not present in March month. Should fetch the YTD sales results for all the products.

[anil]

Any solution for this problem?