locked
lots of years and then YTD RRS feed

  • Question

  • How do I do 7 years full and then YTD for the most current year? I have a measure and then value labels along the columns that are years. Thanks!!
    Wednesday, December 21, 2016 9:38 PM

Answers

  • It depends on what your data look like. When you have new data everyday, you could simply calculate the total for the whole year and then for the current year, it will obviously calculate the YTD (as there's no newer data available).

    On the other hand, if you just want to have the total until today for the current year and totals for the whole year for previous years, the first thing you need to do is test whether the current context (e.g. your year label) is the current year "in real life". You can do this with

    CONTAINS(Date, Date[Date], TODAY())

    assuming you have an appropriate Date table.

    The (year-to-date) total for the current year would be something like

    CALCULATE([BaseMeasure], FILTER(Date, Date[Date] <= TODAY()))

    So a complete formula can look like this:

    IF( 
       CONTAINS(Date, Date[Date], TODAY()),
       CALCULATE([BaseMeasure], 
          FILTER(Date, Date[Date] <= TODAY())
       ),
       [BaseMeasure]
    )
    Note that the reference to the Date table in both the CONTAINS and the FILTER function refers to the Date table in the current context. For example, FILTER takes the Date table (which is filtered to the current year) and filters to only dates before today - resulting in the year-to-date total.

    Wednesday, January 4, 2017 8:31 AM
    Answerer

All replies

  • Alex, any update on this issue?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, January 3, 2017 11:51 PM
  • Hi AlexMartini,

    You can use DATESYTD Function to calculate the YTD for the current year, which returns a table that contains a column of the dates for the year to date, in the current context. The formula given is wrong. It will calculate running total of actuals when you use DATESYTD function in filter. You’d better create a measure like the following formula.

    YTD Sale:=CALCULATE(SUM(Sale[value]),DATESYTD(Datetable[Date]))

    In addition, I am not able to reproduce your scenario based on your description. If this is what you want, please post the sample data for further analysis.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 4, 2017 2:42 AM
  • It depends on what your data look like. When you have new data everyday, you could simply calculate the total for the whole year and then for the current year, it will obviously calculate the YTD (as there's no newer data available).

    On the other hand, if you just want to have the total until today for the current year and totals for the whole year for previous years, the first thing you need to do is test whether the current context (e.g. your year label) is the current year "in real life". You can do this with

    CONTAINS(Date, Date[Date], TODAY())

    assuming you have an appropriate Date table.

    The (year-to-date) total for the current year would be something like

    CALCULATE([BaseMeasure], FILTER(Date, Date[Date] <= TODAY()))

    So a complete formula can look like this:

    IF( 
       CONTAINS(Date, Date[Date], TODAY()),
       CALCULATE([BaseMeasure], 
          FILTER(Date, Date[Date] <= TODAY())
       ),
       [BaseMeasure]
    )
    Note that the reference to the Date table in both the CONTAINS and the FILTER function refers to the Date table in the current context. For example, FILTER takes the Date table (which is filtered to the current year) and filters to only dates before today - resulting in the year-to-date total.

    Wednesday, January 4, 2017 8:31 AM
    Answerer
  • Hi Alex,

    pls get back here and tell us if this answers your question. Then pls mark it as answer, otherwise let us know what's still wrong with it.


    Imke Feldmann TheBIccountant.com

    Sunday, January 15, 2017 7:07 AM
    Answerer