YTD not working when year and month slicer used instead of actual date

[Anonymous]

Hi - I have created YTD measure with below formula - 

OrderQuantity YTD = TOTALYTD(SUM(Fact_Orders[OrderQuantity]),Fact_Orders[OrderedDate])

This is working when I use [OrderedDate] as slicer. But it is not working when I use Year and month in the slicer. Year and month derived from [OrderedDate] field itself. My requirement is to use month year slicer, instead of complete date. 

Can someone suggest what I can do here so that year and month slicer works for YTD.

[mhossain]

@Anonymous 

Try this measure:

OrderQuantity YTD = 

CALCULATE(
                     SUM(Fact_Orders[OrderQuantity]),
                      DATESYTD(Fact_Orders[OrderedDate])
                   )

also would suggest to create DATE dimension table if you haven't created already, and make this dimension table as DATE table.

Let me know if it solves.

[Anonymous]

@mhossain When I create Date dimension table using calendar function, YTD calculation worked. But my report is using direct query, so refresh in PBI service has been failing due to calculated table Calendar. 

Do you suggest any workaround for this? 

[mhossain]

@Anonymous 

I would suggest to create/get created the DATE table in source and access this table in direct query. Can you do that?