Forum Discussion

MagikJukas's avatar
MagikJukas
Resolver III
3 years ago
Solved

Summarize table missing data

Hello, The following Table has been created: Table = SUMMARIZE(Data,'Date list'[Date],Data[Material],"tot",CALCULATE(SUM(Data[Qty]),FILTER(ALLSELECTED('Date list'[Date]),'Date list'[Date]<=MAX...
  • tamerj1's avatar
    tamerj1
    3 years ago

    MagikJukas 
    Like this?

    Measure = 
SUMX (
    ALLSELECTED ( Data[Material] ),
    CALCULATE (
        SUMX (
            VALUES ( 'Date list'[Date] ),
            VAR CurrentDate = 'Date list'[Date]
            VAR Tot =
                CALCULATE (
                    SUM ( Data[Qty] ),
                    'Date list'[Date] <= CurrentDate
                )
            RETURN
                IF ( Tot < 0, Tot, 0 )
        )
    )
)
MagikJukas's avatar
MagikJukas
Resolver III
3 years ago

Hello tamerj1 

fantastic, it works!

I am trying to use your code as a virtual table. I want to extract all the negative numbers in order to sum them up.

 

I got an error though, any idea or hints you can provide?

thank you 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

MagikJukas 
Please try

Measur1 =
SUMX (
    CROSSJOIN ( VALUES ( 'Date list'[Date] ), VALUES ( Data[Material] ) ),
    VAR Tot =
        CALCULATE (
            SUM ( Data[Qty] ),
            FILTER (
                ALLSELECTED ( 'Date list'[Date] ),
                'Date list'[Date] <= MAX ( 'Date list'[Date] )
            ),
            ALL ( Data[Data type] )
        ) + 0
    RETURN
        IF ( Tot < 0, Tot )
)