cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## TOTALYTD until TODAY

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super Contributor

## Re: TOTALYTD until TODAY

@Anonymous

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

Proud to be a Datanaut!

Anonymous
Not applicable

## Re: TOTALYTD until TODAY

@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
)
)```

14 REPLIES 14
Member

## Re: TOTALYTD until TODAY

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).

Super Contributor

## Re: TOTALYTD until TODAY

@Anonymous

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

Proud to be a Datanaut!

Anonymous
Not applicable

## Re: TOTALYTD until TODAY

@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"

Anonymous
Not applicable

## Re: TOTALYTD until TODAY

Owen,

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

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
Not applicable

## Re: TOTALYTD until TODAY

@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
)
)```

Highlighted
Super Contributor

## Re: TOTALYTD until TODAY

@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

Proud to be a Datanaut!

Frequent Visitor

## Re: TOTALYTD until TODAY

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?

Regular Visitor

## Re: TOTALYTD until TODAY

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 ))`

Frequent Visitor

## Re: TOTALYTD until TODAY

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.

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 358 members 3,966 guests
Recent signins: