TOTALYTD Question - Different results

[Pbi07]

Having a question on the way TOTALYTD behaves. 

Trying to get a cumulative totals for the monthly total sales  and receipts from the below data

I have a calendar table and relationship set and able to arrive at the cumulative totals using the TOTALYTD fn. 

calendar = CALENDARAUTO()

When i try using the similar method on a direct query data, my results gets distorted. 

The below YTD_Sales as of Jan resets to 300 instead of 400. 

Year	Month	Sales_Total	Recpt_Total	YTD_Sales
2017	Oct	100		100
2017	Nov			100
2017	Dec			100
2018	Jan	300		300
2018	Feb	600		900

What is causing this kind of difference? 

[amitchandak · Accepted solution]

@Pbi07 , Did you got the answers, If not please share additional information.

[Pbi07]

@v-yuta-msft  @amitchandak 

Thanks @amitchandak for clarifying on the YTD function. I took the below approach to solve it.

M_Cumulative_Total =

VAR MaxDate=CALCULATE(MAX(Calendar[Date]), Invoice)

RETURN CALCULATE([Inv_total], FILTER(ALL(Calendar[Date]), Calendar[Date]<=MaxDate))

[v-yuta-msft]

@Pbi07 ,

Could you please share the sample data and give the measure you are using?

Regards,

Jimmy Tao

[amitchandak]

YTD will reset at the start of year. If you are looking for cumulative

try

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,endofmonth(dateadd(date[date]),-1,month))))