Thursday, April 05, 2012 5:00 PM
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
Thursday, April 05, 2012 6:39 PM
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) ).
Friday, April 06, 2012 8:32 AM
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.
Friday, April 06, 2012 7:03 PMYes, 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?