Filter a measure only in specific case, removing filter on visual

[Anonymous]

Hi, i have a problem with power bi Desktop. I have in a page 2 slicer (Year, Month) and a table that show project name, year, month, pianification period and value. Pianification period is a calculated column that for the month and year choosen in the slicer, it show current month, next month and next 2 months. If i choose from slicer year = 2019 and month = 11, PianificationPeriod show values 11 (current month), 12 (next month) and 1(next 2 month). But attention: this last value of PianificationPeriod must be of the next year (2020). My attended result is this:

PROJECT	YEAR (from slicer)	MONTH (from slicer)	PIANIFICATION PERIOD	VALUE
P1	2019	11	11	123
P1	2019	11	12	456
P1	2019	11	1	789 (value of month 1 of 2020)

The result obtained is this:

PROJECT	YEAR (from slicer)	MONTH (from slicer)	PIANIFICATION PERIOD	VALUE
P1	2019	11	11	123
P1	2019	11	12	456
P1	2019	11	1	0

Here's my measure VALUE and my calculated column PianificationPeriod:

PIANIFICATION_PERIOD = 

SWITCH(RELATED('CONTO_HIER_CP01'[COD_LIV02]);

    "FCT_T"; VALUE(FACT_TABLE[COD_PERIODO]);

    "FCT_T1"; IF(VALUE(FACT_TABLE[COD_PERIODO]) = 12;1;VALUE(FACT_TABLE[COD_PERIODO]) + 1);

    "FCT_T2"; SWITCH(VALUE(FACT_TABLE[COD_PERIODO]);

11;1;

12;2;

VALUE(FACT_TABLE[COD_PERIODO]) + 2

);

    99

)

MEASURE_VALUE = 

IF(SELECTEDVALUE(FACT_TABLE[COD_PERIODO]) = 11 && SELECTEDVALUE(FACT_TABLE[PERIODO_PIANIFICAZIONE]) = 1;

SUMX (

CALCULATETABLE (FACT_TABLE;

FACT_TABLE[COD_SCENARIO]= "2020CPFCT";

FACT_TABLE[COD_PERIODO]=1;

CONTO[TIPO_IMPORTO] = "AMOUNT");

[IMPORTO_CONVERTITO]

)

;

IF(SELECTEDVALUE(FACT_TABLE[COD_PERIODO])=12 && SELECTEDVALUE(FACT_TABLE[PERIODO_PIANIFICAZIONE]) = 1;

121

;

IF(SELECTEDVALUE(FACT_TABLE[COD_PERIODO])=12 && SELECTEDVALUE(FACT_TABLE[PERIODO_PIANIFICAZIONE]) = 2;

122

;

CALCULATE([IMPORTO_CONVERTITO];FILTER(CONTO; CONTO[TIPO_IMPORTO] = "AMOUNT"))

)

)

)

[amitchandak]

@Anonymous , your last row is sowing P1 2019. How come system know it P1 2020.

In case of custom, period create an incremental rank and use that to solve such cases

This period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[period Rank]=max('Date'[period Rank])))
Last period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[period Rank]=max('Date'[period Rank])-1))
Nest 3 period Sales =

Var _min = maxx(allselected('Date','Date'[period Rank])

Var _max = maxx(allselected('Date','Date'[period Rank]) +3

CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))

[Anonymous]

@amitchandak 

Thank you for you reply, but i don't want to use time intelligence for this case.

If the selected month is 11 and the pianification period is 1, i want to remove the filter on year and month to show value of another year and another month.. can i use function ALL to solve this?

We can't use time intelligence because we use the concept of Scenario (instead of year) and Period (instead of month). I used month and year in the first post to simplify the situation.

[amitchandak]

@Anonymous , all will remove all the filter. So better have your period in a different table and use all on that.

I think you can work with non-standard period using rank

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739

In this blog, we already have incremental period no, so did not use rank