Calculation annual change (values in columns)

[Syndicate_Admin]

Hello! I am stuck in my dashboard where I need to make a simple subtraction of some values (Difference in € compared to the previous year), but since I have the layout of the table in columns, I have no way to create a new column with that annual variation (2023-2022):

The table has the following format:

(The data for the year 2023 are below, and what I am interested in including in the formula is the annual variation with respect to " Cod_Taric")

Greetings and thank you in advance!

Jonah O.

[Anonymous · Accepted solution]

Hi  @Syndicate_Admin ,

Because this formula is conformed in my scenario, your PBIX doesn't have this table name, you need to replace the name of each table in the formula with the name of the table in your own PBIX

For example, in your picture: table name: 2022-1

Measure =
var _today=TODAY()
var _2022=
SUMX(
    FILTER(ALLSELECTED('2022-1'),
    '2022-1'[flujo]=MAX('2022-1'[flujo])&&'2022-1'[cod_taric]=MAX('2022-1'[cod_taric])&&
    '2022-1'[ano]=YEAR(_today)-1),[euros])
var _2023=
SUMX(
    FILTER(ALLSELECTED('2022-1'),
    '2022-1'[flujo]=MAX('2022-1'[flujo])&&'2022-1'[cod_taric]=MAX('2022-1'[cod_taric])&&
    '2022-1'[ano]=YEAR(_today)),[euros])    
return
_2023 - _2022

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Thanks Liu!
When inserting the DAX, I get this error - Cannot find table ́Table ́ -

Any suggestions? Thank you very much in advance!

Best regards

[Anonymous]

Hi  @Syndicate_Admin ,

You can try the following dax:

Measure =
var _today=TODAY()
var _2022=
SUMX(
    FILTER(ALLSELECTED('Table'),
    'Table'[flujo]=MAX('Table'[flujo])&&'Table'[cod_taric]=MAX('Table'[cod_taric])&&
    'Table'[ano]=YEAR(_today)-1),[euros])
var _2023=
SUMX(
    FILTER(ALLSELECTED('Table'),
    'Table'[flujo]=MAX('Table'[flujo])&&'Table'[cod_taric]=MAX('Table'[cod_taric])&&
    'Table'[ano]=YEAR(_today)),[euros])    
return
_2023 - _2022

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[littlemojopuppy]

Hi @Syndicate_Admin 

Change your data model.  Unpivot year into a new column in the fact table.  By the way...create a date table too and give year an actual date value (January 1, December 31...doesn't matter).  Then this becomes pretty easy with some time intelligence functions.