SUM depending on that

[ConstantDele]

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

[mark_endicott]

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

[ConstantDele]

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. 

[mark_endicott]

@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:

[ConstantDele]

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

[mark_endicott · Accepted solution]

@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. 

[ConstantDele]

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

[mark_endicott]

@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:

[mark_endicott]

@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. 

[rajendraongole1]

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