Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

USERELATIONSHIP THROWING ERROR

Hello, Below code throws an error "USERELATIONSHIP function can only use the two columns references participating in relationship." Cases Last 13 Month = var MaxDate = MAX('Calendar'[Calendar_la...
Mahesh0016's avatar
Mahesh0016
Super User
3 years ago

Anonymous  please Try this formula.

Cases Last 13 Month =

 

var MaxDate = CALCULATE(

 MAX('Calendar'[Calendar_last_day_of_month])

,USERELATIONSHIP('Calendar'[Calendar_last_day_of_month]

                               ,'Presenatation calendar'[Calendar_last_day_of_month])

                              )

 

var MaxDate_13MonthsAgo =

DATESINPERIOD(

                            'Presenatation calendar'[Calendar_last_day_of_month]

                            ,MaxDate

                            , -13

                           ,MONTH

                         )

var Results =

CALCULATE(

                    Actuals_by_Month[Actuals_Volume],
                    REMOVEFILTERS('Calendar') ,
                    KEEPFILTERS(MaxDate_13MonthsAgo)
)

 

RETURN
Results

 

 

** If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you Mahesh0016 , BUT GETTING SAME ERROR.

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Mahesh0016 ,

      I wish I could share the pbix file after removing the sensitive data but the file is bit of large and removing things could take a lot of time.

      . however my goal is to user should be able to see past 13 months of data based on the month selected. for example. if some one selects "01/31/2023" , user should see past 13 months iof data like below

      Date                Volume
      01/31/2023       10
      12/31/2022       30
      .
      .
      .
      01/31/2022       35

      my input table is also in the same format as above but with one additional column added

      Date                  site    Volume
      01/31/2023        1         5

      01/31/2023         2        5
      12/31/2022        1        10

      12/31/2022        2        10

      12/31/2022        3        10
      .
      .
      .
      01/31/2022        3        35

       

      I am generating calendar like this

      Calendar = CALENDARAUTO()
       
      and presentation calendar = 'Calendar'.
       
      Please let me know if these info help. Thanks