Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hey,
can you please provide the error message you get.
Regards,
Tom
It creates, but when you drag to the canvas that's when l get the error.
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
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
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))
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |