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.
Hi, I'm relatively new to DAX and have a challenge...I have a file with a Year column (no month, no day in any other column). I can't change the year to a date or all years turn to 1905. I want to do a YoY % change calculation. I've tried multiple versions of measures and no luck. Here's a sample of the data:
And here's the desired output:
Any guidance is appreciated. TIA!
Best,
Jeff
Solved! Go to Solution.
Hi @Anonymous
Please add the following calculated measure to your model. I have loaded a PBIX file for you to play with.
% Change = VAR varThisYear = [Sum of Amount] VAR varLastYear = CALCULATE( [Sum of Amount] , FILTER( ALL('Table1'[FiscalYear]), 'Table1'[FiscalYear] = MAX('Table1'[FiscalYear]) - 1) ) RETURN DIVIDE(varThisYear - varLastYear,varLastYear)
Hope this helps
Hi @Anonymous
Please add the following calculated measure to your model. I have loaded a PBIX file for you to play with.
% Change = VAR varThisYear = [Sum of Amount] VAR varLastYear = CALCULATE( [Sum of Amount] , FILTER( ALL('Table1'[FiscalYear]), 'Table1'[FiscalYear] = MAX('Table1'[FiscalYear]) - 1) ) RETURN DIVIDE(varThisYear - varLastYear,varLastYear)
Thanks so much, Phil!!!
And if you want to convert your fiscal year column to a date use the following function, which will create a value of 1st Jan for the particular year (you can use different numbers as the 2nd and 3rd parameters if you would like to pick a date that coincided with your financial year.
= DATE( 'Table1'[FiscalYear] , 1, 1)
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |