topic Re: SUM depending on that in DAX Commands and Tips

SUM depending on that

ConstantDele — Thu, 20 Jun 2024 09:58:31 GMT

Hi everyone, I would like to have a measure that calculates the revenue depending on the year 2023 and 2024. Therefore, I can create another measure to calcultate the % evolution etc.

Do you know how I can do that ?

Thanks a lot

Re: SUM depending on that

rajendraongole1 — Thu, 20 Jun 2024 10:52:41 GMT

Hi ConstantDele - you can create individual measures for 2023 and 2024 for revenue

Eg: Revenue 2023 =
CALCULATE(
SUM(Sales[Revenue]),
YEAR(Sales[Date]) = 2023
)

create another measure for 2024 for revenue.create final measure to calculate the percentage evolution of revenue from 2023 to 2024

Revenue Evolution % =
IF(
[Revenue 2023] = 0,
BLANK(),
([Revenue 2024] - [Revenue 2023]) / [Revenue 2023] * 100
)

check the above calcualtion to get the percentage.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Re: SUM depending on that

mark_endicott — Thu, 20 Jun 2024 11:43:03 GMT

ConstantDele - If you want you can do this in one measure.

% Evolution = VAR _2023 = CALCULATE( SUM( 'Table (3)'[amount] ), YEAR( 'Table (3)'[date] ) = 2023 ) VAR _2024 = CALCULATE( SUM( 'Table (3)'[amount] ), YEAR( 'Table (3)'[date] ) = 2024 ) VAR change = _2024 - _2023 RETURN DIVIDE ( change, _2023 )

If you want separate measures for 2023 and 2024, you can just take the code out of the VAR's for each year and then reference the measures themselves like this:

% Evolution = VAR _2023 = [2023] VAR _2024 = [2024] VAR change = _2024 - _2023 RETURN DIVIDE ( change, _2023 )

If this works for you, please accept as the solution.

Re: SUM depending on that

ConstantDele — Thu, 20 Jun 2024 12:04:53 GMT

Hi All,

thanks for your reply but whenever I do the calculate sum formula, it just takes the whole quantity, and doesn't filter on any year.

Re: SUM depending on that

mark_endicott — Thu, 20 Jun 2024 13:01:56 GMT

ConstantDele - Did you use the DAX formula's below?

2023 = CALCULATE( SUM( 'Table (3)'[amount] ), YEAR( 'Table (3)'[date] ) = 2023 ) 2024 = CALCULATE( SUM( 'Table (3)'[amount] ), YEAR( 'Table (3)'[date] ) = 2024 )

They will only show on rows where the year matches the filter, see below:

Re: SUM depending on that

ConstantDele — Thu, 20 Jun 2024 13:41:27 GMT

Thanks, but whenever I write YEAR, it doesn't push me to write my table, but only (YEAR(DATE)

Re: SUM depending on that

mark_endicott — Thu, 20 Jun 2024 14:02:55 GMT

ConstantDele - You are trying to create a Calculated Column by the sounds of it, you need to create a Measure to use the code I have provided.

Please find and click the icon stating New Measure below:

Re: SUM depending on that

ConstantDele — Tue, 25 Jun 2024 12:43:05 GMT

It worked thanks. What would be the formula if I want to have all the sales from a specific Month ?

And what is the formula to have the qty sold in the current month ?

Thanks

Re: SUM depending on that

mark_endicott — Tue, 25 Jun 2024 14:49:16 GMT

ConstantDele - As my DAX has worked, please could you mark it as the solution, this helps other users find it.

To find a specific month, you can amend the DAX to take in a month input also, the below will work for current month.

VAR _Month = MONTH(NOW()) VAR _Year = YEAR(NOW()) RETURN CALCULATE( SUM( 'Table (3)'[amount] ), YEAR( 'Table (3)'[date] ) = _Year && MONTH( 'Table (3)'[date] ) = _Month )

To change this to a specific month, you can amend the variables (VAR) to show a number from 1 to 12 for any specific month, and a year, for example:

VAR _Month = 6 VAR _Year = 2024 RETURN CALCULATE( SUM( 'Table (3)'[amount] ), YEAR( 'Table (3)'[date] ) = _Year && MONTH( 'Table (3)'[date] ) = _Month )

The column inside the SUM( ) can also be changed for your QTY sold column.

Re: SUM depending on that

mark_endicott — Thu, 27 Jun 2024 09:43:25 GMT

ConstantDele - please could you mark it as the solution, this helps other users find it.