cancel
Showing results for
Did you mean:
Frequent Visitor

## Adding a new column to compare previous year

Hi Everyone.

I have this table below. I'd like to create another column similar to Total Invoice with the same data for the previous year. I am not sure if I need to create a date table or simply use a formula for the date range I am looking for?

From what I've read so far, theres a previous year function (DAX) except i think this requires  a unique date table if Im not mistaken. So i am not sure what would be the best approach.

I've tried the following formula with no success as I keep getting an error:

2016 = CALCULATE(SUM('Contractor Expense'[TotalInvoice]), DATESBETWEEN('Contractor Expense'[ToDate],11/28/2015,07/30/2016))

The current Total Invoice column is filtered for 11/29/2016 to 07/30/2017.

3 REPLIES 3
Microsoft

## Re: Adding a new column to compare previous year

Hi @maximus1,

Could you try using the formula below to create measure to see if it works in your scenario?

```PY Total Invoice =
VAR maxDate =
MAX ( 'Contractor Expense'[ToDate] )
VAR minDate =
MIN ( 'Contractor Expense'[ToDate] )
VAR jobCode =
MAX ( 'Contractor Expense'[Job Code] )
RETURN
CALCULATE (
SUM ( 'Contractor Expense'[TotalInvoice] ),
FILTER (
ALL ( 'Contractor Expense' ),
'Contractor Expense'[Job Code] = jobCode
&& 'Contractor Expense'[ToDate]
>= DATE ( YEAR ( minDate ) - 1, MONTH ( minDate ), DAY ( minDate ) )
&& 'Contractor Expense'[ToDate]
<= DATE ( YEAR ( maxDate ) - 1, MONTH ( maxDate ), DAY ( maxDate ) )
)
)
```

Regards

Frequent Visitor

## Re: Adding a new column to compare previous year

Thank you @v-ljerr-msft

It semi worked. some of the values were spot on while others were a bit off. One of the big issues now is that I know I have values in the PY (for different job descriptions) that I dont have in the Total Invoice (this year), but they are not showing up. I believe these values arent showing up because the formula is only returning prior year values for what is on the existing table. Not sure how to resolve that but I am trying to do some research on creating a date table to see if that will solve my issues. I sincierly appreciate the help, and if you have any other suggestions I am all ears. I am closer today than I was yesterday, so that some progress!

Microsoft

## Re: Adding a new column to compare previous year

Hi @maximus1,

Could you share a sample pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,724)