cancel
Showing results for
Did you mean:
Frequent Visitor

## 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:

And here's the desired output:

Any guidance is appreciated. TIA!

Best,

Jeff

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

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

Hi @JeffF

```% 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)    ```

Proud to be a Datanaut!

4 REPLIES 4
Super Contributor

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

Hi @JeffF

```% 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)    ```

Proud to be a Datanaut!

Super Contributor

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

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)`

Proud to be a Datanaut!

Established Member

Hope this helps

Frequent Visitor

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

Thanks so much, Phil!!!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 56 members 1,159 guests
Recent signins: