Forum Discussion

fcaliman's avatar
fcaliman
New Member
8 years ago
Solved

DAX: Sumif equivalent filtering by selected value

Hello,

 

I have table with 2 columns: month(Number) and Value. I am trying to build a table that would show the sum of the accumulated values by month. So i`ve tried to create a measure like this:

 

Measure= CALCULATE(SUM('Table[Value]);'Table'[Month] > SELECTEDVALUE('Table'[Month])

 

Unfortunatly it didnt work because it is not allowed to use the function "SELECTEDVALUE" in filter criterias.

 

Does anyone have a solution for this?

 

  • MFelix's avatar
    MFelix
    8 years ago

    Hi fcaliman,

     

    If your month is based on number maybe you can use:

     

    Measure =
CALCULATE (
    SUM ( Table[Value] );
    FILTER ( ALL ( Table[Month] ); Table[Month] < MAX ( Table[Month] ) )
)

    Regards,

    MFelix

4 Replies

  • fcaliman's avatar
    fcaliman
    New Member
    8 years ago

    Hello,

     

    I have table with 2 columns: month(Number) and Value. I am trying to build a table that would show the sum of the accumulated values by month. So i`ve tried to create a measure like this:

     

    Measure= CALCULATE(SUM('Table[Value]);'Table'[Month] > SELECTEDVALUE('Table'[Month])

     

    Unfortunatly it didnt work because it is not allowed to use the function "SELECTEDVALUE" in filter criterias.

     

    Does anyone have a solution for this?

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      8 years ago

      Hi fcaliman,

       

      If your month is based on number maybe you can use:

       

      Measure =
CALCULATE (
    SUM ( Table[Value] );
    FILTER ( ALL ( Table[Month] ); Table[Month] < MAX ( Table[Month] ) )
)

      Regards,

      MFelix

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi,

       

      "Accumulation over time period" questions are very easy to solve if you use the Date/Time Intelligence functions.  You must have a Date column in your data to use those functions.  Do you have a Date column?  If not, we can always build a date field if you have Year and Month in seperate columns.  Do you have year and Month columns in your base data.  If yes, then share that data and show the expected result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI

     

    So, you want to select some value in slicer and calculate the measure accordingly ?

     

    The below one will work, but make sure if you select multiple values in slicer, the selected value will return blank.

    Measure 3 = CALCULATE(SUM(Table1[Value]),FILTER(Table1,MONTH(Table1[Date])>MONTH(SELECTEDVALUE(Table1[Date]))))

     

    Thanks
    Raj