TotalYTD is returning Monthly total

[petermb72]

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

[mwegener]

Hi @petermb72 ,

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

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.

[kentyler]

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.

[petermb72]

I 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

[v-lid-msft]

Hi @petermb72 ,

We can try to create a measure use following formula to meet your requirement:

Total YTD 4 =
VAR y =
    YEAR ( MAX ( 'AccountSummary'[Date] ) )
RETURN
    CALCULATE (
        SUM ( 'AccountSummary'[Total] ),
        FILTER (
            ALLSELECTED ( 'AccountSummary' ),
            [Date]
                >= DATE ( y - 1, 10, 1 )
                && [Date] <= DATE ( y, 9, 30 )
        )
    )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[mwegener]

Hi @petermb72 ,

check this out, for a date table with fiscal calendar:

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[petermb72]

So you need to have matching fields in the date calendar as well?? Now that is something that is not in the instructions that I have read.  I will give that a try.

[v-lid-msft]

Hi @petermb72 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[mwegener]

Hi @petermb72 ,

YTD aggregates over the date table, so this field must be part of the date table. Otherwise it will slice the data by this fields.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @petermb72 ,

Year and Period ID must be part of your date table.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[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.