The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hello,
Below code throws an error "USERELATIONSHIP function can only use the two columns references participating in relationship."
@priyanath1988 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.
@priyanath1988 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
37 | |
36 | |
30 | |
29 | |
21 |
User | Count |
---|---|
61 | |
41 | |
32 | |
18 | |
16 |