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 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.

 

 

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.

2 Replies

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

     

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    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.