Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |