Using TOTALYTD

Question

Hi,

I'm using TOTALYTD and I see the related syntax: TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>]).

For my workbook I've a time dimension composed of two columns, year and month. I think to create a measure with TOTALYTD expression with DATE() expression as the second argument. So, I've created a third time columns as concatenating year and month. In my fact table, I've also year and month. I've created a calculated column by concatenating year and month. I've create a relationship between time and the fact table by yearandmonth column.

When I try to write the expression TOTALYTD(sum(MyFactTable[myamount]); Date(Time[Year]; Time[Month]; 1) ) I've an error about "Calculation error in measure 'TimeReport'[YTD]: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

So, I cannot use DATE inside TOTALYTD, isn'it? Any suggests to me, please? Thanks

Answer 1

Hi Pscorca

the <dates> parameter really should be used only to specify a column that contains a list of dates, preferrably from your 'Date' table (that is, a table with sequential dates with no gaps).   In that sense, date expressions should not be used (like  Date(Time[Year]; Time[Month]; 1) ).

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Hi Javier, thanks for your reply.

It seems a bit strange that I cannot use the function Date() that allows to build a calculated date by a year (that is a column), a month (that is another columns). In other terms, in this case Date() begins a sort of dynamic column.

However, if I have a temporal dimension with only year and month how can I determine the TOTALYTD? Do I need to have a time dimension with contiguous dates? You know that a such time dimension has duplicates for the same year and month. Really, it isn't necessary to use contiguous date as dimension filter, but simply years and months.

Many thanks

Answer 3

Yes, TOTALTYTD only accepts a column of dates as a second parameter.  It is best practice to have a separate table but is not required.  What it is required is that is an existing column, so you cannot use an expression.  You can, however, use the expression in a calculated column, and then pass this column to TOTALYTD. Does that make sense?

---

Javier Guillen
http://javierguillen.wordpress.com/