Forum Discussion

petermb72's avatar
petermb72
Icon for Helper IV rankHelper IV
6 years ago

TotalYTD 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 t...
Need Help
kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

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's avatar
petermb72
Icon for Helper IV rankHelper IV
6 years ago

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
  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    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.

    • petermb72's avatar
      petermb72
      Icon for Helper IV rankHelper IV
      6 years ago

      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.

      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        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.

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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,