Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Get Previous Running Total for Missing Month

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...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

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

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])