cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Prior and Current Year total sales calculation direct query

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Prior and Current Year total sales calculation direct query

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
5 REPLIES 5
Super User
Super User

Re: Prior and Current Year total sales calculation direct query

Hey,

 

can you please provide the error message you get.

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Anonymous
Not applicable

Re: Prior and Current Year total sales calculation direct query

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

 

Capture.PNG

 

Super User
Super User

Re: Prior and Current Year total sales calculation direct query

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Anonymous
Not applicable

Re: Prior and Current Year total sales calculation direct query

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))
)
)

 

Anonymous
Not applicable

Re: Prior and Current Year total sales calculation direct query

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