Accumulative calculation over 2 databases

[justinleow]

Dear All,

Thank you so much in advance to help me with my this problem as I cannot figure how to make it work. I am currently making a PowerBI dashbaord on sales performance report, which gets data from 2 databases. (1) is the product databse with inidival sales targets, and also a transaction database where all individual sales is made.

See a dummy database below,
 

This is the a output i an able to achieved currently (in tabular form). What I need is to have a report that show the percentage of the sales compard to the sales target of individual products in a monthly trend. The way I acheive this is definitely not the most efficient way as I am very new to DAX. 

I created a calculated table by summerize the "Sales database" to unqiue product ID, which then I lookupvalue the sales target of individual prodcut ID from the product database into the new calculated table. Then have a calculated measure to calculate the sales performance as shown in the achieved tabel below. 

However, my final expected output is not just deplaying the performance of individual months but a accumulative one over the 6 months. I am not able to do that as my projected sales target do not have a time definitation.

EXPECTED OUTPUT

Thank you so much for giving me some ideas or guidance to how to achieve it. Deeply thank you for your teachings.

Best Regards,

Justin Leow

[v-shex-msft · Accepted solution]

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