Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

YoY difference on Year (Whole Number) field, Not Date field

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:Sample Raw Data.GIF

 

 

And here's the desired output:Sample Output.GIF

Any guidance is appreciated. TIA!

Best,

Jeff

 

 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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)    

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Hardik
Continued Contributor
Continued Contributor

Screenshot_6.pngHope this helps

Phil_Seamark
Employee
Employee

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)    

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

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)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.