Limit KPI by time range in set analysis using variables

Question

I want to be able to limit a KPI, let's say turnover, to a specific time range which I chose by setting two variables: the lower bound and the upper bound.

I have created two tables, which I derive from the calendar-table.

month_start:
LOAD MonthYear as MonthStart
RESIDENT Calendar;

month_end:
LOAD MonthYear as MonthEnd
RESIDENT Calendar;

I create two filters, one for MonthStart and one for MonthEnd.

I set two variables. I know that GetFieldSelections()returns a string so I convert it back to a date.

=date#(GetFieldSelections(MonthEnd), 'MM YYYY')

=date#(GetFieldSelections(MonthStart), 'MM YYYY')

Finally I use this expression to calculate the KPI:

SUM({$< MonthYear = {"<$(=vEndMonth)>=$(=vStartMonth)"}>} [turnover])

But it doesn't work. I get 0,00€.

What am I doing wrong? What am I missing?

EDIT:

Example Data:

LOAD *
Inline [
%date, country, turnover
01.01.2021, DE, 1000
10.01.2021, AT, 2000
23.01.2021, CH, 1500
12.02.2021, DE, 2300
23.02.2021, DE, 5000
02.02.2021, CH, 1200
09.03.2021, AT, 3000
10.03.2021, CH, 1000
31.03.2021, DE, 3400
01.04.2021, CH, 2200
]

There is a calendar attached to the %date-field with the MonthYear-field which is created as monthname(%date) as MonthYear.

As result I want a Pivot Table with country as row-dimension and MonthYear as column. The measure is a master element with above formula.

Answer 1

Please create selection box for MonthYear and just select there needed values with Ctrl.

Please share small table with your data and desired output as table so I will share with you exact example script something like:

SUM({$< MonthYear = {"<$(=Max(MonthYear))>=$(=Min(MonthYear))"}>} [turnover])

Answer 2

You could do with months, month/years etc. But the easiest way is to use dates, both in your calendar and in your variables.

Make sure your calendar have a non aggregated date like this:

Calendar:
Load
TempDate            as %date,
Date(TempDate)      as Date,
monthname(TempDate) as MonthYear,
Month(TempDate)     as Month,
Year(TempDate)      as year

Then create your end and start fields like this:

[month_start]:
Load Distinct
MonthStart(Date) as MonthStart
RESIDENT Calendar;

[month_end]:
Load Distinct
MonthEnd(Date) as MonthEnd
RESIDENT Calendar;

Your variables are ok. Then create your measure like this (see that the field is the "Date" field from the Calendar:

SUM(
    {$<
        Date = {">=$(=vStartMonth)<$(=vEndMonth)"}
    >}
[turnover])

Should work. Let me know.

Best Regards,