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.
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.
Solved! Go to 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.
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.
Thanks 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 ,
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.
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.
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 |
---|---|
105 | |
105 | |
86 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |