Forum Discussion
AdministratorCalculation annual change (values in columns)
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.
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 - _2022Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- littlemojopuppy
Community Champion
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.
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 - _2022Best 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_AdminThanks Liu!
When inserting the DAX, I get this error - Cannot find table ́Table ́ -Any suggestions? Thank you very much in advance!
Best regards
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 - _2022Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
