Forum Discussion
Helper IVTotalYTD is returning Monthly total
Facts:
- We run on a Fiscal Year October 1 to September 30.
- I have a date table that has a date for every day that goes from 10/1/2014 to 9/30/2030.
- I have a calculated Montly total
- The main table has one date line per Month with the date of first day of the month (3/1/2019)and the Montly Balance. It also has the Fiscal Year, and Fiscal Period.
- formula I am trying to use to get YTD looks Like: TotalYTD = Calculate ([MontlyTotal],DatesYTD(DateTable[Date],"9/30"))
What I get for a return is the same as a monthly total. I am sure this is something strange that I am just not grasping for the YTD functions to work. I have been banging my head against a wall trying to figure this one out and with no luck.
Thanks,
Peter
11 Replies
- kentyler
Solution Sage
Hello,
The formula you are using is missing one of the optional arguments, the filter: formula I am trying to use to get YTD looks Like: TotalYTD = Calculate ([MontlyTotal],DatesYTD(DateTable[Date],"9/30"))
Here is the sample from Microsoft's online documentation:
=TOTALYTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey], ALL(‘DateTime’), “6/30”)
The ALL function removes any filters that may have been placed on the calendar by other visuals on the report
The filter argument and the year end argument are optional, but if you want to use the year end argument you need to also have the filter argument....otherwise dax will try to interpret your year end valueas a filter.
I'm a personal Power BI trainer I learn something new every time I answer a question.
- kentyler
I should have been clearer.
If you placed your measure in table, when it ran the filter context in which it would be running would be the month of that row....so its "total" would be the same as the total for the month, as there would be no other months in the filter context for it to add up
When you add the ALL() as the filter, The all removes the filters from your calendar table, so all the months are available, and then the function can add up all the months in the ytd.
All DAX measure run INSIDE AN OUTER FILTER CONTEXT.... this context is not "visible" if the forumla for the measure, but it has a profound effect on the results it returns. 
petermb72I used the following formula:
Total YTD3 = TOTALYTD(SUM(AccountSummary[Total]),Dates[Date],ALL(Dates),"9/30")It returned the monthly total for each month of the year, not the YTD. date table
- mwegener
Most Valuable Professional
Hi petermb72 ,
how you calculated the Monthly total?
I think the problem is, that you use the Measure [MontlyTotal] which aggregate the values only for a month and not for a year.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
