Prior and Current Year total sales calculation direct query

[Anonymous]

Hi Gurus,

I have connected to  HANA DB direct query. I need help in calculating  Previous Year & Current Year total sales.

I try this formula from Pragmaticworks.com cheat sheet but I'm getting an error. Please help

Prior Year Profit (Direct Query)

Calculates Profit for all days in the Year prior to the last year in the selection. Limited to the last day of the selection. Prior Year Profit = CALCULATE ( [Profit], FILTER ( ALL ( 'DateTable' ), 'DateTable'[Year] = MAX ( 'DateTable'[Year] ) - 1 ) )

Thanks

PC

[TomMartens]

Hey,

can you please provide the error message you get.

Regards,

Tom

[Anonymous]

It creates, but when you drag to the canvas that's when l get the error.

[TomMartens · Accepted solution]

Hey,

this simply says that the columns have different data types, make your that both columns have the same data type.

I'm wondering if it's a typo that you use the column Destination.

Nevertheless, mark the column and in the Modeling menu you will find "Data type" in the formatting ribbon.

As you subtract -1 to get the prior year, the data type should be numeric. Also consider that it might be necessary to use this snippet 

YEAR(MAX('tablename'[date column))

if the column on the right hand side of the filter condition has the data type date

Regards,

Tom

[Anonymous]

Yes the Destination was a typo, but more so the 

data type 

LIKE BTW is a whole number

 WADA YEAR  is Text 

I can't change it to whole number

Thanks

PC

[Anonymous]

Thanks for your help

So this will be my new formula.

Prior Year =CALCULATE (
[Profit],
FILTER (
ALL ( 'DateTable' ),
YEAR(MAX('tablename'[date column)) - 1
)
)

Current Year =CALCULATE (
[Profit],
FILTER (
ALL ( 'DateTable' ),
YEAR(MAX('tablename'[date column))
)
)