Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 ea...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
atowriss1
Icon for Advocate I rankAdvocate I
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      atowriss1
      Icon for Advocate I rankAdvocate I
      3 years ago

      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