time intelligence (current year -1) calc issue with related table

[ogend]

Hello, 

I have a model with the  revenue by department data, calendar( date table), and related table with tax rates per department

I am building a report where user can select current year from a list and the  prior year  automatically becomes current year -1 with time intelligence approach via date table

my prior  year revenue measure is:

Revenue PY = var t= MAX('date'[Year])-1 var result= CALCULATE(SUM('Revenue'[Revenue]),'Revenue'[Version]="Actual", 'calendar'[CYear]=t) return result

I do get corrent result for Prior Year Revenue and it correctly recalculates when I select a different year from the slicer

However when i am doing a simple tax calculation with 

PY Tax= CALCULATE(SUMX('Revenue',[Revenue PY]*RELATED(taxRates[Tax Rates]))) I keep getting blank output 

Obviously i am doing this wrong . can someone please explain why this is happening and recommend a fix?

[richbenmintz]

Hi @ogend,

Given that I haven't seen your model or how each of the tables are related, I would suggest that you create a measure that does the sumx over your base revenue attribute, then create your PY Tax value using the base measure. I hope that makes sense.

For a working Measure please provide a sample pbix file that I or another community member can work with.

Thanks,

[ogend]

Thanks, @richbenmintz 

 here are the files

I would appreciate if someone can explain what i am doing wrong here- why PY tax measure is not returning any values

Thank you!

Olga

[richbenmintz · Accepted solution]

Hi @ogend,

The issue is with your relationships, the tax rate is not related to the date directly and the bi-directional relationship does not get back to the date correctly, but if you change the way your measure works and reference the CY Tax Measure as the expression for your measure it works.

Tax PY =
CALCULATE ( Revenue1[Tax CY], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )

or you could re-write your current measure like so: 

- Get the Current Amount in the context of the Revenue table, then push it back a year.

PY Tax =
CALCULATE (
    SUMX ( 'Revenue1', [Revenue] * RELATED ( taxRates[rate] ) ),
    Revenue1[Scenario] = "Actual",
    PARALLELPERIOD ( 'Date'[Date], -1, YEAR )
)

Sorry that you had to wait until moring EST, but it was a school night 😆

[ogend]

@richbenmintz  Thank you so much! you saved me :)!

Olga

[richbenmintz]

My pleasure, 

Happy to help out!