Forum Discussion
Adding a new column to compare previous year
Microsoft EmployeeHi maximus1,
Could you try using the formula below to create measure to see if it works in your scenario? :smileyhappy:
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
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!
- v-ljerr-msft
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. :smileyhappy:
Regards