cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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? Smiley Happy

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: TOTALYTD until TODAY

@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 Smiley Happy



Did I answer your question? Mark my post as a solution!

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

 

 

13 REPLIES 13
bsas Member
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 User
Super User

Re: TOTALYTD until TODAY

@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 Smiley Happy



Did I answer your question? Mark my post as a solution!

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,

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 Smiley Happy


 

 

 

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 User
Super User

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 Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




taylorrc Frequent Visitor
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?

doubles Regular Visitor
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 ))

 

 

wverheijen Frequent Visitor
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.