Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Little Challenge Folks

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 ...

data protection
General Comment
Interesting Links
Tips and Tricks
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.