Forum Discussion

elf_ranger91's avatar
elf_ranger91
Frequent Visitor
9 years ago

TOTALYTD incorrect values

Hi guys I hope someone can help me out here.

 

I´m having some difficulty with the TOTALYTD function. Here is what I have written in my DAX formula: 

 

YTD Revenue FY16 CLP = TOTALYTD(SUM('FY16 Journal Details'[Monto FY16 CLP]);'FY16 Journal Details'[Effective Date];'Account FY16'[ISG1]="REVENUE";ALL('FY16 Journal Details'[Effective Date]))

 

However, when I put YTD Revenue in a table, I do not get cumulative ytd values. Actually they are the same as the monthly revenue values. Furthermore, the total amount is incorrect (it should be the year total no? the same as total revenue). Here is the table below:

 

What´s going on here?

 

Note: the reason that October is first is because that is when our fiscal year begins.  

 

Thanks

 

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    If I am breaking down your formula correctly:

     

    YTD Revenue FY16 CLP =

     

    TOTALYTD(SUM('FY16 Journal Details'[Monto FY16 CLP]); - So the first parameter you are telling it to SUM your [Monto FY16 CLP] field. That's fine.

     

    'FY16 Journal Details'[Effective Date]; - Here you are passing in a column of dates. That's fine.

     

    'Account FY16'[ISG1]="REVENUE"; - Now you have passed in a filter to only include columns that are REVENUE. This should be fine as long as the tables are related. In theory, somehow your Account table is related to your FY16 Journal Details so you should only be pulling FY16 Journal Details that relate back to an Account that is tagged as "REVENUE", correct?

     

    ALL('FY16 Journal Details'[Effective Date])) - This one I don't understand, the fourth parameter should be the specification of an End Date for the year. It looks like instead you are passing it in an entire column of dates which means it will probably just grab the first date.

     

     

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi elf_ranger91,

     

    In your scenario, please check the syntax of TOTALYTD() function firstly:

     

    TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>]) 

     

    The fourth parameter needs to be “A literal string with a date that defines the year-end date. The default is December 31.” You can try the modify the DAX like below:

     

    YTD Revenue FY16 CLP = TOTALYTD(SUM('FY16 Journal Details'[Monto FY16 CLP]);'FY16 Journal Details'[Effective Date];'Account FY16'[ISG1]="REVENUE";"9/30")

     

    Best Regards,
    Qiuyun Yu

    • danielH's avatar
      danielH
      Helper I
      9 years ago

      I have the same issue. 

      what is weird is that a have a tile which works, and in the other tile doesnt. with basically same input, diferent Data sources. 

      is there a solution for this ? 

       

       

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        danielH

        Can you post the formula of the Measure you are using?

        Are you specifying the fiscal year end? (as mentioned above)

        For example if your FY ends June 30

        MEASURE 1

        YTD Total =
TOTALYTD ( SUM ( Table[Column] ), 'CalendarTable'[Date], "06/30" )

        or alternatively

        MEASURE 2

        YTD Total ALT =
CALCULATE ( SUM ( Table[Column] ), DATESYTD ( 'CalendarTable'[Date], "06/30" ) )

         

        Good Luck! :smileyhappy: