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.

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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.

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

    • 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,