TOTALYTD cannot get to work

Question

TOTALYTD cannot get to work

sotn

SSCrazy

Points: 2672
More actions
December 2, 2014 at 1:44 am

#313506

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

sotn (12/2/2014)
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

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

sotn SSCrazy Points: 2672 More actions · Answer 2

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.

sotn SSCrazy Points: 2672 More actions · Answer 3

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