December 2, 2014 at 1:44 am
Hi, I cannot seem to get the TOTALYTD function to work
this is in Tabular mode, sql 2014, and directquery is set to off
The Dim_Date table is marked as a date table, and the field 'DateKey' which is a 'date' is the primary/unique key and I have a relationship setup between the DocumentPostingDate on the Fact table and the DateKey on the Dim_Date table
my calculated field is this
YTDSales:=TOTALYTD([SalesAmountEUR],Dim_Date[DateKey])
For this version I get the error message
Measure 'vw_GM_FACT'[YTDSales] : The value for 'SalesAmountEUR' cannot be determined. Either 'SalesAmountEUR' doesn't exist, or there is no current row for a column named 'SalesAmountEUR'.
I have also tried setting the 2nd parameter to the date field in the FACT table
YTDSales:=TOTALYTD([SalesAmountEUR],[DocumentPostingDate])
For this version I get the error message
Measure 'vw_GM_FACT'[YTDSales] : The value for 'DocumentPostingDate' cannot be determined. Either 'DocumentPostingDate' doesn't exist, or there is no current row for a column named 'DocumentPostingDate'.
There are different examples on the web with ' surrounding the Dim_Date table but I have tried all permutations I believe but with no difference.
Thanks for any help
December 2, 2014 at 8:07 pm
sotn (12/2/2014)
Hi, I cannot seem to get the TOTALYTD function to workthis is in Tabular mode, sql 2014, and directquery is set to off
The Dim_Date table is marked as a date table, and the field 'DateKey' which is a 'date' is the primary/unique key and I have a relationship setup between the DocumentPostingDate on the Fact table and the DateKey on the Dim_Date table
my calculated field is this
YTDSales:=TOTALYTD([SalesAmountEUR],Dim_Date[DateKey])
For this version I get the error message
Measure 'vw_GM_FACT'[YTDSales] : The value for 'SalesAmountEUR' cannot be determined. Either 'SalesAmountEUR' doesn't exist, or there is no current row for a column named 'SalesAmountEUR'.
I have also tried setting the 2nd parameter to the date field in the FACT table
YTDSales:=TOTALYTD([SalesAmountEUR],[DocumentPostingDate])
For this version I get the error message
Measure 'vw_GM_FACT'[YTDSales] : The value for 'DocumentPostingDate' cannot be determined. Either 'DocumentPostingDate' doesn't exist, or there is no current row for a column named 'DocumentPostingDate'.
There are different examples on the web with ' surrounding the Dim_Date table but I have tried all permutations I believe but with no difference.
Thanks for any help
Start with something like
YTDSales:=TOTALYTD( SUM ( vw_GM_Fact[SalesAmountEUR]) ,Dim_Date[DateKey])
I am guessing that column SalesAmountEUR is in table vm_GM_Fact. If it isn't, then use the correct table name instead.
I also suspect that the date column may be incorrect. Not sure on that without knowing what your model looks like but you may need to use the datekey from vw_GM_Fact
December 3, 2014 at 2:21 am
thanks for the reply.
It has almost worked, no error, but no value
Yes salesamountEUR is in the vw_GM_FACT table, that table(view) has a 'date' field which has a relationship to my dim_date table which has as its primary key the field DateKey which is of type 'date' and has 1 value for every day, and there all records in vw_GM_Fact match a dim_Date record based on the 'date field' .
So now with this as the formula
YTDSales:=TOTALYTD(SUM(vw_GM_FACT[SalesAmountEUR]),Dim_Date[DateKey])
or with
YTDSales:=TOTALYTD(SUM([SalesAmountEUR]),Dim_Date[DateKey])
I now get in the bottom part of the screen
YTDSales: (blank)
So no errors, but no values either
The SalesAmountEUR field is a decimal(38,20) and each record has a value
I have changed the properties of the field in SSAS to have a data format of 'decimal number' instead of 'general' but no difference. It was already set to a 'date type' of 'decimal number'.
I can do a normal, Sum of SalesAmountEUR:=SUM([SalesAmountEUR])
with no problem.
December 3, 2014 at 3:58 am
I have made a bit more progress
It seems that as my fact table does not have a record for every single day, then I get the (blanks)
If I change my view to start with dim_date, so I get a record for every day and use a 0 for those days without any facts, then in SSAS, I get a value, but a value of 0, so it is not totalling it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy