Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

USERELATIONSHIP with PREVIOUSYEAR returns blanks

I've turned off time intelligence for my report, and am using a Dates Table, and it's marked as a Dates Table.

 

I want the sum of the Billed Amount (basically sales) for the prior year. This is easy when I don't use a Dates Table - I just add in a PREVIOUSYEAR function. 

 

But with a Dates Table, PREVIOUSYEAR doesn't work for me, and I think it's because I don't have the .[Year] portion of that formula. Here's what I have: 

 

CALCULATE(
[Billed Amount by QtrEndDate],     <<this value is where I employed USERELATIONSHIP to sum the Billed Amount
PREVIOUSYEAR('Dates table'[Date]))     <<This is supposed to end in 'Dates table'[Date].[Year]
 
This gets me a blank result, as does this: 
 
CALCULATE(
sum(BillingHistory[BilledAmount]),    <<this is just the Billed Amount without USERELATIONSHIP 
USERELATIONSHIP('Dates table'[Date],BillingHistory[QtrEndDate]),
PREVIOUSYEAR('Dates table'[Date]))
 
I've tried adding in a date hierarchy to QtrEndDate and even to another date field in the table, but neither help me - I can't seem to get that .[Year] that I think the formula needs. 
 
Anyone got any ideas? Thank you!
5 REPLIES 5
Anonymous
Not applicable

After many hours of experiments, I finally solved it this way: 

 

BILLED AMOUNT, PY =

VAR _PriorYear = Year(TODAY()-365)
return
CALCULATE(
sum(BillingHistory[BilledAmount]),
USERELATIONSHIP('Dates table'[Date],BillingHistory[QtrEndDate]),
FILTER('Dates table','Dates table'[Date].[Year]=_PriorYear)
)
 
And I was only able to make it work by unmarking my Dates Table as a Dates Table. 
 
No matter what I tried, PREVIOUSYEAR refused to work, it would either return a blank or it would return all amounts for all time.

@Anonymous , I was having the same issue with blanks, and used your formula without the userelationship piece, as my dates are just month end dates, and I am totaling by month.  But, the calculation is only summing Feb -Aug and not Jan-Aug.  Any idea why that might be?

Thx

Anonymous
Not applicable

Unsure, but it may have something to do with the structure of your dates table and whether it's actually marked as a dates table. I have run into the same issue in the past, but can't recall how I resolved it, though I feel like it had to do with the dates - like maybe I wasn't including the .[Year] portion of the measure.

@Anonymous ...I had an aha moment this morning and checked my date table.  I had a typo and it was starting on 2/1 as opposed to 1/1.  

Thanks for posting your solution....it really helped me out

amitchandak
Super User
Super User

@Anonymous , Try like

 

CALCULATE(CALCULATE(
sum(BillingHistory[BilledAmount]),
USERELATIONSHIP('Dates table'[Date],BillingHistory[QtrEndDate])),
PREVIOUSYEAR('Dates table'[Date]))

 

QtrEndDate , should not have a timestamp

 

slicer, filter, and visual should use the period from Dates table. and it should be marked as date table

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.