Forum Discussion
TOTALYTD Question - Different results
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.
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?
Anonymous , Did you got the answers, If not please share additional information.
4 Replies
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))))
Anonymous ,
Could you please share the sample data and give the measure you are using?
Regards,
Jimmy Tao
Anonymous , Did you got the answers, If not please share additional information.
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))
