Calculate YTD across all year using one measure

[themistoklis]

Hello All,

In the attached file I have data for 3 years (2019, 2020 and 2021).

For 2021, I have data upto March 2021.

I want to create a measure where I can add it in a table with year dimension + the YTD measure and and it will show data from Jan till Mar in each year

Thank you

[jdbuchanan71 · Accepted solution]

@themistoklis 

Not sure I fully understand but I think I do.  I added a column to your 'Calendar Date Hour' table to check if the day of the year is <= TODAY()

Year day is past = 
    VAR _Year = YEAR ( 'Calendar Date Hour'[Date] )
    VAR _Month = MONTH ( TODAY() )
    VAR _Day = DAY ( TODAY() )
    VAR _CompareDate = DATE ( _Year, _Month, _Day )
RETURN 'Calendar Date Hour'[Date]  <= _CompareDate

Then a measure to use that as a filter.

YTD Amount = CALCULATE ( SUM('Input Data'[VIEWS_HOURLY_FILTERED]), 'Calendar Date Hour'[Year day is past] = TRUE )

I have attached my updated copy of your file.

A couple of notes.  In your model you shouldn't have time in your calendar table.  If you need time you should split it into a seperate table and link them both into your fact tables.

You also want your date tables to go from Jan 1 of the first year to Dec 31 of the last year.  I have run into odd behavior when the date table contains partial years.

[themistoklis]

Hello @jdbuchanan71 ,

This is what i wanted. I will make a few tweaks to Year day is past formula.

First Time that I use Time in Calendar table and I had a few difficulties to create certain measures because of time that i had to use. As you suggested maybe it is best if i split it into a separate table.

Great workaround though

Thank you