Loosing the filter context after return last value for empty values

[Anonymous]

I have a dataset with a period and values for each period. I need to return values for active period and when period is inactive I need to return the last value non empty. I could get the values in the table, but when I apply filters (e.g. filter by country the values in my measure named LastValue dont change the numbers).

Without filter, I could get the expected results in the column "Last Value".

The measure for column ActiveValue = CALCULATE( SUMX( 'Table', 'Table'[Value] ), FILTER( 'Table', 'Table'[Active] == "Yes" ) )

The measure for column LastValue = VAR LastValue = CALCULATE( MAXX( 'Table', 'Table'[Period] ), FILTER( ALL( 'Table' ), 'Table', 'Table'[Period] <= MAX( 'Table', 'Table'[Period] ) && [ActiveValue] ) )

RETURN CALCULATE( [ActiveValue], FILTER( ALL( 'Table' ), 'Table', 'Table'[Period] == LastValue ) )

Period	Active	Value	ActiveValue	LastValue
2020M01	Yes	1000	1000	1000
2020M02	Yes	1300	1300	1300
2020M03	No	900		1300
2020M04	No	800		1300

But, when I apply any filter, e.g. for country and select a specific country the values change but not the LastValue:

Period	Active	Value	ActiveValue	LastValue
2020M01	Yes	800	800	1000
2020M02	Yes	850	850	1300
2020M03	No	700		1300
2020M04	No	500		1300

After use the filter the expected result were:

Period	Active	Value	ActiveValue	LastValue
2020M01	Yes	800	800	800
2020M02	Yes	850	850	850
2020M03	No	700		850
2020M04	No	500		850

Does anybody knows how to apply the filter context for the measure LastValue?

[smpa01 · Accepted solution]

Hello @Anonymous 

Hopefully, the Fact Table  is connected to some Date Table so that the Period can be assigned to some sort of ranking like following

|                    Fact                    |
|---------|--------|-------|---------|-------|
| Period  | Active | Value | Country | Index |
|---------|--------|-------|---------|-------|
| 2020M01 | Yes    | 562   | USA     | 1     |
| 2020M02 | Yes    | 681   | USA     | 2     |
| 2020M03 | No     | 691   | USA     | 3     |
| 2020M04 | No     | 973   | USA     | 4     |
| 2021M01 | No     | 690   | USA     | 5     |
| 2021M02 | Yes    | 705   | USA     | 6     |
| 2021M03 | No     | 516   | USA     | 7     |
| 2021M04 | No     | 821   | USA     | 8     |
| 2020M01 | No     | 819   | Canada  | 9     |
| 2020M02 | No     | 562   | Canada  | 10    |
| 2020M03 | Yes    | 544   | Canada  | 11    |
| 2020M04 | No     | 566   | Canada  | 12    |
| 2021M01 | No     | 768   | Canada  | 13    |
| 2021M02 | Yes    | 725   | Canada  | 14    |
| 2021M03 | No     | 845   | Canada  | 15    |
| 2021M04 | No     | 548   | Canada  | 16    |

|       Date           |
|---------|------------|
| Period  | PeriodRank |
| 2020M01 | 1          |
| 2020M02 | 2          |
| 2020M03 | 3          |
| 2020M04 | 4          |
| 2021M01 | 5          |
| 2021M02 | 6          |
| 2021M03 | 7          |
| 2021M04 | 8          |

ActiveValue = CALCULATE( SUMX( 'Fact', 'Fact'[Value] ), FILTER( 'Fact', 'Fact'[Active] == "Yes" ) )

Measure = 
VAR _periodRanking =
    MAX ( 'Date'[PeriodRank] )
VAR _lastNonBlankPeriod =
    CALCULATE (
        MAX ( 'Date'[PeriodRank] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[PeriodRank] <= _periodRanking
                && [ActiveValue] <> 0
        )
    )
VAR _sum =
    CALCULATE (
        [ActiveValue],
        ALL ( 'Date' ),
        'Date'[PeriodRank] = _lastNonBlankPeriod
    )
RETURN
    _sum

The pbix is attached here

https://1drv.ms/u/s!AkrysYUHaNRvhcV6Kt10cbzc9aM-wg?e=TjV9vj

Can you please check and let me know if it is responding to the slicers? My test suggestes that it does.

Edit

Just in case you want the measure to roll up on the subtotal level as well

Measure 3 = SUMX(ADDCOLUMNS(CROSSJOIN(SUMMARIZE('Fact','Fact'[Country]),'Date'),"@X",[Measure]),[@X])

[Anonymous]

Works fine, thank you @smpa01!

[AlexisOlson]

In your measure, you are using ALL( 'Table' ). This function removes all filter context on the entire table so any filters you've applied are gone.

I'd try something that only involves removing/replacing filters on Period. More like this:

LastVal =
VAR CurrPeriod = MAX ( 'Table'[Period] )
VAR LastPeriod =
    CALCULATE ( MAX ( 'Table'[Period] ), 'Table'[Period] < CurrPeriod )
RETURN
    CALCULATE ( [ActiveValue], 'Table'[Period] = LastPeriod )

[Anonymous]

When I remove the ALL('Table') I miss the fill all empty values with the last non empty value. Using something that only involves removing/replacing filters on Period the return working with the filters, but the result is similiar with measure ActiveValue. The result was:

Period	Active	Value	ActiveValue	LastValue
2020M01	Yes	800	800	780
2020M02	Yes	850	850	800
2020M03	No	700		850
2020M04	No	500

The expected is (in case of filter by country)

Period	Active	Value	ActiveValue	LastValue
2020M01	Yes	800	800	800
2020M02	Yes	850	850	850
2020M03	No	700		850
2020M04	No	500		850