DAX: Sumif equivalent filtering by selected value

[fcaliman]

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?

[fcaliman]

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?

[Ashish_Mathur]

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.

[MFelix · Accepted solution]

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

[Anonymous]

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