Get Previous Running Total for Missing Month

[Anonymous]

Hi All,

I want to make accumulative line graph of which line should always increase.

- Even though there is missing month for each item, the month should exist with previous value.

so the graph must not decrease or cut for the missing month.

Let say I have two tables like below, the 'Date' table and 'ShipQty' table has relation in date column.

X axis: Date, Y axis: Qty

Please kindly help me.

Thanks in advance.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

ShipQty:

Date:

Date = CALENDAR(DATE(2001,1,1),DATE(2022,12,31))

There is a relationship between two tables.

You may create  calculated columns and a measure as below.

Calculated column:
YearMonth = VALUE(FORMAT('Date'[Date],"yyyymm"))
YearMonthDay = FORMAT('Date'[Date],"yyyy-mm-"&"01")
YearMonth2 = VALUE(FORMAT(ShipQty[Date],"yyyymm"))

Measure:
Result = 
CALCULATE(
    SUM(ShipQty[Qty]),
    FILTER(
        ALL(ShipQty),
        ShipQty[Item] = SELECTEDVALUE(ShipQty[Item])&&
        ShipQty[YearMonth2]<=SELECTEDVALUE('Date'[YearMonth])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Right, so normally the way you tackle this is to have a separate, (sometimes unrelated) calendar table with all the dates. You use the date column from this in your visualization and code your measure like this:

Cumulative Measure =
  VAR __Date = MAX('Calendar'[Date])
RETURN
  SUMX(FILTER('Table','Table'[Date] <= __Date),[Column])