USERELATIONSHIP THROWING ERROR

[Anonymous]

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_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),

USERELATIONSHIP('Calendar'[Calendar_last_day_of_month],'Presenatation calendar'[Calendar_last_day_of_month]))

RETURN

 Results

Notes: I already have inactive relationship established between Calendare table and presentation table
Could someone can tell whats wrong here?

Thanks

[Mahesh0016]

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

Thank you @Mahesh0016 , BUT GETTING SAME ERROR.

[Mahesh0016]

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

[Anonymous]

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