Forum Discussion

SUW003's avatar
SUW003
New Member
5 years ago

Running total on pivot table when no data

Hi all,

I'm creating report for stock requirements.

As on following, we have several items. Not all date have transaction on it.

I can create running total, but my current issue is, on pivot table, it shows blank if there is no data on the date.

Any suggestion ?

colRecShip = STOCK_REQUIREMENT[RECEIPT] + STOCK_REQUIREMENT[SHIPMENT]

Available = CALCULATE(

SUM(STOCK_REQUIREMENT[colRecShip]),
FILTER(
ALL(STOCK_REQUIREMENT),
STOCKREQUIREMENT[Date]<=MAX(STOCK_REQUIREMENT[Date])
),
VALUES(STOCK_REQUIREMENT[Item])
)
 

Need Help
Show and Tell

1 Reply

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    SUW003 

    Try the following measure:

    Measure no blanks =
VAR lstdate =
    MAXX (
        FILTER (
            ALL ( Calendar table ),
            Calendar table [Date] < MAX ( Calendar table [Date] )
                && NOT ( ISBLANK ( [your measure] ) )
        ),
        Calendar table [Date]
    )
VAR prev =
    CALCULATE (
        [your measure],
        FILTER ( ALL ( Calendar table ), Calendar table [Date] = lstdate )
    )
RETURN
    IF (
        ISBLANK ( [your measure] ),
        prev,
        [your measure]
    )