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 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!
Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

  • 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.