Forum Discussion
How to filter a column
I am trying to subtract column 2019 from 2020 but I can't because I can't get the columns to filter from FY to 2019, 2020, 2021.
This is my matrix in power bi (this is Excel just for example).
When I try to create a formula, the column shows as GL[FY], I can't get it to show the specifically named column GL[FY2019], etc. in order to get the formula to work correctly.
I hope I explained this clearly.
Thanks!
Hi kaytrishjr ,
According to the data model you provided, the Net (FTYD) values corresponding to all these years are in the same row. The dax formula cannot directly perform calculations similar to 2020-2019, and the system cannot determine the specific corresponding row. But you can operate on aggregated values, refer to the following formula:
M_1 = VAR a = SUMX ( FILTER ( 'Table', 'Table'[FY] = 2020 ), SUM ( 'Table'[NET(FYTD)] ) ) VAR b = SUMX ( FILTER ( 'Table', 'Table'[FY] = 2021 ), SUM ( 'Table'[NET(FYTD)] ) ) RETURN b - aIf you want to divide according to categories and find the difference in a specific year, you may need to make appropriate changes to the data model.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
In Power BI you use measures to do math on your data. Have you had exposure to DAX coding yet?
Hi kaytrishjr ,
According to the data model you provided, the Net (FTYD) values corresponding to all these years are in the same row. The dax formula cannot directly perform calculations similar to 2020-2019, and the system cannot determine the specific corresponding row. But you can operate on aggregated values, refer to the following formula:
M_1 = VAR a = SUMX ( FILTER ( 'Table', 'Table'[FY] = 2020 ), SUM ( 'Table'[NET(FYTD)] ) ) VAR b = SUMX ( FILTER ( 'Table', 'Table'[FY] = 2021 ), SUM ( 'Table'[NET(FYTD)] ) ) RETURN b - aIf you want to divide according to categories and find the difference in a specific year, you may need to make appropriate changes to the data model.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
