Accumulative calculation over 2 databases

HI justinleow,

You can't direct achieve rolling total on actual sale table, because some products missed records of specific range.
I'd like to suggest you create new table to add missed records, then write a formula to calculate running total.

Expand table:

Expand = 
VAR temp =
    SELECTCOLUMNS ( Sales, "Product ID", [Product ID], "Sale month", [Sale month] )
VAR fulltable =
    SELECTCOLUMNS (
        CROSSJOIN (
            VALUES ( Sales[Product ID] ),
            VALUES ( Sales[Sale month] )
        ),
        "Product ID", [Product ID],
        "Sale month", [Sale month]
    )
RETURN
FILTER(
    UNION (
        Sales,
        SELECTCOLUMNS (
            EXCEPT ( fulltable, temp ),
            "TransactID", 0,
            "Product ID", [Product ID],
            "Sale month", [Sale month],
            "Sales Amount", 0
        )
    ), [Product ID] <> "Cancelled" )

Measures:

Actual total = 
CALCULATE (
    SUM ( Expand[Sales Amount] ),
    FILTER (
        ALLSELECTED ( Expand ),
        [Product ID] IN VALUES ( Expand[Product ID] )
            && [Sale month] <= MAX ( [Sale month] )
    )
)

Total Product = 
CALCULATE (
    SUM ( 'Product'[Monthly Projectedsales[*divide by 6]]),
    FILTER (
        ALLSELECTED ( 'Product' ),
        [ProductID] IN VALUES ( 'Product'[ProductID] )
            && [Sale month] <= MAX ( [Sale month] )
    )
)

Percent = 
VAR Actual =
    CALCULATE (
        SUM ( Expand[Sales Amount] ),
        FILTER (
            ALLSELECTED ( Expand ),
            [Product ID] IN VALUES ( 'Product'[ProductID] )
                && [Sale month] <= MAX ( 'Product'[Sale month] )
        )
    )
RETURN
    Actual / [Total Product]

Regards,

Xiaoxin Sheng