TOTALYTD until TODAY

[Anonymous]

Dear all,

I´m trying a "simple" measure to calculate the cumulative values from the beginning of the year until "today".

The formula below works, but notice I´m fixing the "year_end_date"

YTD Values =
TOTALYTD([Total Values];Dates[Dates];ALL(Dates[Dates]);"9/5")

However, if I try to set up "9/5" as a formula, like "TODAY()", or other vars, it gives me an error.

YTD Values =
var Today = MONTH(TODAY()) & "/" & DAY(TODAY())
return
TOTALYTD([Total Values];Dates[Dates];ALL(Dates[Dates]);Today)

Help? 

[OwenAuger · Accepted solution]

@Anonymous

Just clarifying your requirements...

If today is 5-Sep-2017, then what date range do you expect the formula to calculate over?

1. 1-Jan-2017 to 5-Sep-2017 (cumulative total as at today, but with years ending on 31-Dec) 
2. 6-Sep-2016 to 5-Sep-2017 (cumulative total as at today, and with years ending on today's date, which would be the same as summing the last year ending today)
3. Something else?

As a side note, in the TOTALYTD or DATESYTD functions, year_end_date can never be any expression apart from a string literal. If you want a flexible year_end_date, you have to write the time intelligence logic from scratch without using the built-in time intelligence functions.

Owen 🙂

[Anonymous]

I use the IF(MAX function to get blanks after today

YTD Values = IF(MAX('Dates'[Date]) > TODAY() , BLANK(), CALCULATE(TOTALYTD([Total Values], 'Dates'[Date])))

[OwenAuger · Accepted solution]

@Anonymous

Just clarifying your requirements...

If today is 5-Sep-2017, then what date range do you expect the formula to calculate over?

1. 1-Jan-2017 to 5-Sep-2017 (cumulative total as at today, but with years ending on 31-Dec) 
2. 6-Sep-2016 to 5-Sep-2017 (cumulative total as at today, and with years ending on today's date, which would be the same as summing the last year ending today)
3. Something else?

As a side note, in the TOTALYTD or DATESYTD functions, year_end_date can never be any expression apart from a string literal. If you want a flexible year_end_date, you have to write the time intelligence logic from scratch without using the built-in time intelligence functions.

Owen 🙂

[Anonymous]

Owen,

Thanks for the reply.

If today is 5-Sep-2017, I´d like 1-Jan-2017 to 5-Sep-2017, and so on, until 31-Dec.

In this case, I guess TOTALYTD, as you mentioned, won´t work for me, right?

---

@OwenAuger wrote:

@Anonymous

Just clarifying your requirements...

If today is 5-Sep-2017, then what date range do you expect the formula to calculate over?

1. 1-Jan-2017 to 5-Sep-2017 (cumulative total as at today, but with years ending on 31-Dec) 
2. 6-Sep-2016 to 5-Sep-2017 (cumulative total as at today, and with years ending on today's date, which would be the same as summing the last year ending today)
3. Something else?

As a side note, in the TOTALYTD or DATESYTD functions, year_end_date can never be any expression apart from a string literal. If you want a flexible year_end_date, you have to write the time intelligence logic from scratch without using the built-in time intelligence functions.

Owen 🙂

---

[Anonymous · Accepted solution]

@OwenAuger

From your insight, I came to this scratch formula that seems to work for me.

Thank you!

YTD Values = 
var ActualMonthBeginning = DATE(YEAR(TODAY());MONTH(TODAY());01)
var YearBeginning = DATE(YEAR(MIN(Dates[dates]));01;01)
return
   CALCULATE([Total Values]
      ;FILTER(Values
         ;Values[Dates] < ActualMonthBeginning
         && Values[Dates] >= YearBeginning
      )
   )

[OwenAuger]

@Anonymous

That's good if the formula gives the desired result!

I would just propose this simper alternative:

Given that your year_end_date is the default of 31-Dec, and you are simply calculating a cumulative total as at "today", you can get away with a simpler formula.

Also, it would be best practice to apply date filters only to the Dates table. I am assuming there is a relationship between Values[Dates] and Dates[dates].

YTD Values =
CALCULATE (
    TOTALYTD ( [Total Values]; Dates[dates] );
    TREATAS ( { TODAY () }; Dates[dates] )
)

You should only need to write YTD time intelligence logic if you have a 'flexible' year-end date, but in your case it is just the date filter that needs to be flexible (set equal to TODAY()).

Owen 🙂

[jlinn]

@OwenAuger  you're the man! It worked!

YTD Pipeline Creation Goal =

CALCULATE (

TOTALYTD ( SUM('Model'[Pipeline Creation Goal]), 'Date'[date] ),

TREATAS ( { TODAY () }, 'Date'[date] )

)

[wverheijen]

Hi @OwenAuger,

Thank you for this solution:

YTD Values =
CALCULATE (
    TOTALYTD ( [Total Values]; Dates[dates] );
    TREATAS ( { TODAY () }; Dates[dates] )
)

How would you do this if you need this result for multiple years?

Example:

2016 (01 jan - TODAY)       x

2017 (01 jan - TODAY)       x

2018 (01 jan - TODAY)       x

And so on.

Your formula now gives the result of 01 jan 2016 until TODAY.

Thank you.

[OwenAuger]

@wverheijen

Just checking exactly what you want here:

Do you want "today" (for example 20 June 2018) to be translated into each calendar year?

So if today is 20 June 2018, then when filtering on 2016, you will get the result for 1 Jan 2016 to 20 June 2016?

[wverheijen]

@OwenAuger

thanks for your reply!

This is right, but it's not my intention to filter on a specific year. I want to display all available years in a table with values from 1 Jan to 20 June 2016.

[OwenAuger]

@wverheijen

Here's one idea - it's designed to translate TODAY () into all visible years in the current filter context, and evaluate the measure in the context of YTD dates within those years.

YTD Values =
VAR TodayDate =
    TODAY ()
VAR TodayMonth =
    MONTH ( TodayDate )
VAR TodayDay =
    DAY ( TodayDate )
VAR YearDateFilter =
    GENERATE (
        VALUES ( Dates[year] ),
        VAR TodayInCurrentYear =
            DATE ( Dates[year], TodayMonth, TodayDay )
        RETURN
            CALCULATETABLE (
                DATESYTD ( Dates[dates] ),
                TREATAS ( { TodayInCurrentYear }, Dates[dates] )
            )
    )
RETURN
    CALCULATE ( [Total Value], KEEPFILTERS ( YearDateFilter ) )

[wverheijen]

@OwenAuger @Anonymous

I got the exact answer in my own post, thank you very much for your input aswell!

https://community.powerbi.com/t5/Desktop/YTD-of-years-until-today/m-p/443733#M205094

[taylorrc]

This worked great for me. Thanks!

I have a question thought. I'm also comparing to same period last year. I'm using the formula:

Last Year YTD=Calulate ([CurrentYearYTD]), SAMEPERIODLASTYEAR(DATESYTD('Table'[Date])))

This is returning the same value at the CurrentYearYTD calcluation. Any suggestions to make this comparison?

[doubles]

I was trying to do the same thing, only over 4 years. I ended up creating a calendar table with a DayOfYear column and then filtering on data with a dayofyear less than or equal to the day numer of  today, so I was able to compare the sums up to a particular day  in each year.

SumAmtYtd = CALCULATE(SUM(Table[Amount]),FILTER(Dates,Dates[DayNoOfYear] <= DATEDIFF ( DATE ( YEAR ( TODAY()), 1, 1 ), TODAY(), DAY ) + 1 ))

[Anonymous]

Is the column of the "DayOfYear" = Day('Calender'[Date])? 

I tried your solution, but the SumAmtYtd returns the same value of the CurrentAmt. Could you please give me more detiails? 

Thanks a lot! 

[bsas]

Hi,

I think the problem is that TOTALYTD do not see your var today as date. Try to use full date (e.g. 2/5/2017).

[Anonymous]

---

@bsas wrote:

Hi,

I think the problem is that TOTALYTD do not see your var today as date. Try to use full date (e.g. 2/5/2017).

---

Thank you for the reply, Bsas,

However, it didn´t work. I tried both adding "TODAY()" to end of the formula, also adding "var Today = TODAY()", and also setting DATE(2017,9,5), but I get an error message saying:

"True/false expression does not specify a column. Each expression in a True/False expression that is used as a table filter expression"