Filter a table with single column by a value from measure

[Joerg]

Dear All

First of all I have to apologize if I am asking a question which might be stupid, but I am fairly new and didn't find a solution for my problem by searching since a few days.

I have 2 tables, one giving me some periods, the other the numbers from 1 to 12.

All_Periods:

Year_Month	Index
2017-01	1
2017-02	2
2017-03	3
2017-04	4
2017-05	5
2017-06	6
2017-07	7
2017-08	8
2017-09	9
2017-10	10
2017-11	11
2017-12	12
2018-01	13
2018-02	14
2018-03	15
2018-04	16
2018-05	17
2018-06	18
2018-07	19
2018-08	20
2018-09	21
2018-10	22
2018-11	23
2018-12	24
2019-01	25
2019-02	26
2019-03	27
2019-04	28
2019-05	29

NoOfPeriods:

NoOfPeriods
1
2
3
4
5
6
7
8
9
10
11
12

I have for each table a slicer, letting me select a period and a number of periods I want to look backwards (incl. the selected period).

I created a measure to get the slicer selection for the period:

SelectedPeriodEnd = SELECTEDVALUE(All_Periods[Year_Month];"Select a Value")

The measure to get the index of the selected period from column "Index":

IndexSelected = LOOKUPVALUE(All_Periods[Index];All_Periods[Year_Month];All_Periods[SelectedPeriodEnd])

Then I am defining the count of Periods available backwards basing on the selected period, which shall not exeed 12:

MaxPeriod = if(
            if(HASONEVALUE(All_Periods[Year_Month]);
            LOOKUPVALUE(All_Periods[Index];All_Periods[Year_Month];All_Periods[SelectedPeriodEnd]);
            MAX(All_Periods[Year_Month]))>12;
            12;
            if(HASONEVALUE(All_Periods[Year_Month]);
            LOOKUPVALUE(All_Periods[Index];All_Periods[Year_Month];All_Periods[SelectedPeriodEnd]);
            MAX(All_Periods[Year_Month])))

For the value from the second slicer I created a measure, ensuring the selected number of periods does not exeed the MaxPeriod:

Anz.Period = if(SELECTEDVALUE(NoOfPeriods[NoOfPeriods];"Select a Number")<=All_Periods[MaxPeriod];
SELECTEDVALUE(NoOfPeriods[NoOfPeriods];"Select a Number");
if(SELECTEDVALUE(All_Periods[Year_Month];"Select a Value")>All_Periods[MaxPeriod];All_Periods[MaxPeriod];SELECTEDVALUE(All_Periods[Year_Month];"Select a Value")))

Now I am able to define the StartPeriod:

StartPeriod = LOOKUPVALUE(All_Periods[Year_Month];All_Periods[Index];All_Periods[IndexSelected]-(All_Periods[Anz.Period]-1))

Choosing period 2018-06 and number of periods 8 returns me following values:

SelectedPeriodEnd: 2018-06

StartPeriod: 2017-11

With these values I would like to create a visual table like:

SubTable A = CALCULATETABLE(All_Periods;FILTER(All_Periods; All_Periods[Year_Month]<="2018-06");FILTER(All_Periods; All_Periods[Year_Month]>="2017-11"))

but I like to use my measures "SelectedPeriodEnd" and "StartPeriod" instead of static values, in order to make it dynamic.

When I try

SubTable A = CALCULATETABLE(All_Periods;FILTER(All_Periods; All_Periods[Year_Month]<=[SelectedPeriodEnd]);FILTER(All_Periods; All_Periods[Year_Month]>=[StartPeriod]))

I always get the full range of periods in the table.

What am I doing wrong here ?

Best regards

Joerg

[v-yuta-msft]

@Joerg ,

Based on your description, there're two many filtres apply on table 'ALL_Periods' like the slicer and filter statement in calculate table. So in general, I would suggest you to create an additional date table with the date column then create the slicer based on the date column, do not make relationship between the new date table and 'ALL_Periods'.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joerg]

Dear Jimmy

Even with a simple model my attempt does not work. I can't use the measure 'Slicervalue' to filter the table.

I have a table with Fruits and Values, a second table with Slicervalues from 1 to 5.

Setting up a calculated filtered table does not work with the value from the Slicervalue.

Can you broaden my horizons ?

Cheers

Joerg