Dynamic Filter in a Measure

[kohlivinayak]

Hi I am creating the below measure 

M_Freight Variance = CALCULATE(sum(Book2[Freight]),Book2[PC]="PC04 FY18")-CALCULATE(sum(Book2[Freight]),Book2[PC]="PC3 FY 18 REV")

In this the values on which data is being filter are coming from excel file/ data fields, these values need to be dynamic as one excel file will contain ony two such values whenever a new excel file is loaded these filters need to change according to data and there are multiple values which are used, so can't predfine them.

How to do this ?

[kohlivinayak]

Max will work in case of two pc's only, i have pc from 1 to 8 with financial year changing, so no to pc will be same.

[v-sihou-msft]

@kohlivinayak

In this scenario, you should have a FY column and a PC column in your table. Both columns should be numeric so that we can get the "Previous" one in FILTER(). 

Then you can write a measure like below: 

M_Freight Variance =
CALCULATE ( SUM ( Book2[Freight] ) )
    - CALCULATE (
        SUM ( Book2[Freight] ),
        FILTER (
            ALL ( Book2 ),
            Book2[PC]
                = MAX ( Book2[PC] ) - 1
                && Book2[FY] = MAX ( Book2[FY] )
        )
    )

Regards,

[kohlivinayak · Accepted solution]

I am using a measure where i am calcuting total sales and filtering it on a particular column.

M_Freight Variance = CALCULATE(sum(Book2[Freight]),Book2[PC]="PC04 FY18")-CALCULATE(sum(Book2[Freight]),Book2[PC]="PC3 FY 18 REV")

these values keep on changing pc3,pc4,pc5 etc and in one file on which the report is built will have only two such values. 

I want to pick these values from excel column and filter on the bases of them.

How to do so ?