Forum Discussion
SimonNagy
2 years agoFrequent Visitor
Custom KPI for time-intelligence in table with adjustable time resolution
Hello All,
I've been working on a table, in which the date resolution can be changed with a parameter group from day to week to month, as follows:
At the same time, I'm trying to implement a custom measure, which would check the difference between today/yesterday; this month/last month etc. and output an arrow, which I would place in the table. The measure is looking like this atm:
change_performed_ex_during_the_day =
VAR Selected = SELECTEDVALUE( resolutionSwitch[resolutionSwitch Fields], "Day" )
VAR _difference =
SWITCH(
TRUE(),
Selected = "Day", SUM('APTRISPL FACT_examinations_report_2'[performed_examinations_during_the_day]) -[prev_day_perf_examinations],
Selected = "Week", SUM('APTRISPL FACT_examinations_report_2'[performed_examinations_during_the_day]) -[prev_week_perf_examinations],
Selected = "Month", SUM('APTRISPL FACT_examinations_report_2'[performed_examinations_during_the_day]) -[prev_month_perf_examinations],
Selected = "Year", SUM('APTRISPL FACT_examinations_report_2'[performed_examinations_during_the_day]) - [prev_year_perf_examinations],
BLANK()
)
VAR _currentDate = MAX(DateWithWeeks[Day])
VAR _direction =
SWITCH(
TRUE(),
_difference > 0, "▲",
_difference < 0, "▼",
"⬤"
)
RETURN
IF (
_currentDate >= DATE(2023, 7, 1) && _currentDate <= MAX('APTRISPL FACT_examinations_report_2'[date]),
_direction,
BLANK() -- Return blank if the date is outside the range
)
I am kind of stuck at this point, as the output is always the circle (i.e., difference is 0).
Thank you very much for your help in advance!
Simon
SELECTEDVALUE doesn't work with field parameters. Use the pattern below:
VAR Selected = SELECTCOLUMNS ( SUMMARIZE ( 'Parameter', 'Parameter'[Parameter], 'Parameter'[Parameter Fields] ), "Parameter", 'Parameter'[Parameter] )
1 Reply
- DataInsightsSuper User
SELECTEDVALUE doesn't work with field parameters. Use the pattern below:
VAR Selected = SELECTCOLUMNS ( SUMMARIZE ( 'Parameter', 'Parameter'[Parameter], 'Parameter'[Parameter Fields] ), "Parameter", 'Parameter'[Parameter] )