Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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 😆
Proud to be a Super User!
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,
Proud to be a Super User!
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
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 😆
Proud to be a Super User!
My pleasure,
Happy to help out!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |