Little Challenge Folks

[Anonymous]

Hello I'm Trying to find the Lupmed sum of a data in a table based on a crtiria selected by the Yearly slicer. Essentialy, if i select on the Yearly slicer for instance period from 2011 to 2018. I want a measure to return the lump sum of all the Year selected from MainRawData table except the last Year selected from a Yearl Slicer i.e. the Max year in this case 2018.

Last Year Excluded

I have tried this code but i couldnot earese the last raw.

Calculate(sumx(MainRawData, 'MainRawData'[Q1]+'MainRawData'[Q2]+'MainRawData'[Q3]+'MainRawData'[Q4]),FILTER(MainRawData, 
'MainRawData'[Year] = SelectedValue(SelectedYrs[Year] )

Any help on this would really appreciate ...

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, don't use selectedvalue-function which will return one single value, use values-function instead, here's my solution.

CALCULATE (
    SUMX (
        MainRawData,
        'MainRawData'[Q1] + 'MainRawData'[Q2] + 'MainRawData'[Q3] + 'MainRawData'[Q4]
    ),
    FILTER (
        MainRawData,
        'MainRawData'[Year]
            IN VALUES ( SelectedYrs[Year] )
                && 'MainRawData'[Year] <> MAX ( SelectedYrs[Year] )
    )
)

I create a sample and get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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