USERELATIONSHIP with PREVIOUSYEAR returns blanks

[Anonymous]

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!

[Anonymous]

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.

[atowriss1]

@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]

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.

[atowriss1]

@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]

@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-bd52912a5b...