Forum Discussion

SimonNagy's avatar
SimonNagy
Frequent Visitor
2 years ago
Solved

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

  • SimonNagy,

     

    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

  • SimonNagy,

     

    SELECTEDVALUE doesn't work with field parameters. Use the pattern below:

     

    VAR Selected =
        SELECTCOLUMNS (
            SUMMARIZE ( 'Parameter', 'Parameter'[Parameter], 'Parameter'[Parameter Fields] ),
            "Parameter", 'Parameter'[Parameter]
        )