FILTER using column values instead of a scalar value

[ryanxngo]

I am trying to create a cumulative sum measure using the following formula:

Cumulative_Cash =

VAR CurrentRowDate = MAX(FactSales[CalendarDate])

VAR SelectedGrains = VALUES(FactSales[Grain])

RETURN CALCULATE(

    SUM(FactSales[Total_Cash]),

    FactSales[Grain] = SelectedGrains,

    FILTER(

        ALL(FactSales),

        FactSales[CalendarDate] <= CurrentRowDate

    )

)

Within the FILTER function, I have an additional condition for Grain:
FactSales[Grain] = SelectedGrains
If it is only one selection, it works but if I select two or more grains it gives error: A table of multiple values was supplied where a single value was expected.

Is there anyway to filter the FactSales[Grain] column to the values selected in the SelectedGrains table variable?

[ryanxngo]

Hi ValtteriN,
Thank you so much, I thought this had worked for me but seems like there's one more issue:
I have a month columns based on Date so if I use the Month column the cumulative sum doesn't work as expected

Here is my dataset:

The formula that I used:

C_Cash =

VAR CurrentRowDate = MAX(FactSales[Date])

RETURN

CALCULATE(

    SUM(FactSales[Total_Cash]),      

        ALL(FactSales[Date]),

        FactSales[Date] <= CurrentRowDate

    )

I hope you can help me with this as well, thanks

[FreemanZ · Accepted solution]

hi @ryanxngo ,

try like:

1) add a calculated column like:

MonthNO = FORMAT([Date], "YYYYMM")

 2) plot a table visual with month column and a measure like:

C_Cash = 
SUMX(
    FILTER(
        ALLSELECTED(data), 
        data[MonthNO]<=MAX(data[MonthNO])
    ),
    data[Cash]
)

or 

C_Cash2 = 
CALCULATE(
    SUM(data[Cash]),
    ALLSELECTED(data), 
    data[MonthNO]<=MAX(data[MonthNO])
)

it worked like:

[ValtteriN]

Hi,

Here is how to do this:

Data:

Dax:

Cumulative_Cash =

VAR CurrentRowDate = MAX(FactSales[Date])

RETURN

CALCULATE(

    SUM(FactSales[Total_Cash]),      

        ALL(FactSales[Date]),

        FactSales[Date] <= CurrentRowDate

    )

End result:

The calculation seems to work as expected considering the test data.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/