none
In TOTALYTD function is it possible to avoid hard-coding year_end_date?

    Question

  • Suppose you have a fiscal year of 9/30. The TOTALYTD function looks like:

      YTD Actual $:=TOTALYTD( [Actual $], 'Date'[Date], ALL('Date'), "9/30")

    But I want to avoid hard coding "9/30", and assuming I have a settings table from where I can get a date with the yearend, I tried this: 

      YTD Actual $:=TOTALYTD ([Actual $], 'Date'[Date], ALL('Date'), DISTINCT( 'Settings'[YearEndDate]))

    I get no errors but it calculates like "12/31". I tried to use Format but with no luck.

    Is there a way to make this work?

    Monday, December 16, 2013 11:56 PM

Answers

All replies

  • I didn't try it, but I guess a max should work (assuming that there is only one date in the settings table).

    YTD Actual $:=TOTALYTD ([Actual $], 'Date'[Date], ALL('Date'), MAX( 'Settings'[YearEndDate])

    Can you try it out?


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, December 17, 2013 2:51 PM
    Answerer
  • Thanks Jason,

    I tried adding the MAX (and removing the DISTINCT) but I get an error:

    "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

    Wonder what this table filter accepts.

    Regards,

    José

    Tuesday, December 17, 2013 7:31 PM
  • Oops, sorry, my mistake. 

    By the way, this blog talks about the same thing I guess

    http://paultebraak.wordpress.com/2011/12/16/dax-ytd-on-445-calender-and-irregular-end-dates/


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Tuesday, December 17, 2013 7:39 PM
    Answerer