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