Forum Discussion

ryanxngo's avatar
ryanxngo
New Member
3 years ago
Solved

FILTER using column values instead of a scalar value

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]) ...
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    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's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
3 years ago

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/