Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |