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
OwenAuger Super Contributor
Super Contributor

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

 

 

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

OwenAuger Super Contributor
Super Contributor

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
OwenAuger Super Contributor
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 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.

 

 

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 358 members 3,966 guests
Please welcome our newest community members: