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

Hi again tamerj1 

thanks for the code, but that does not work. the reuslts is showing a lot of blank rows.


your code for the static table works exactly as I wanted. I just need to figure out how to replicate it has a virtual table.

 

I am attaching the link of the file, I cleaned up the data to keep it light to the essential.

https://drive.google.com/file/d/19tcvWiDSksTOsQcgdptCibiujXKO1vyh/view?usp=share_link

 

I would appreciate if you gave it a look.

 

regards

 

tamerj1's avatar
tamerj1
Community Champion
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

    just fantastic!

    thanks a lot