Running Weekly Total Combining Two Data Sources

[MiloPowerBI]

Hello all, 

I'm looking to create a Matrix (or table) that displays Weekly Planned Targets for each week of the Quarter, which then turn into Actual numbers as the Quarter progresses. So for any week that is in the Current Week or in the future, it displays the Planned Target and if its a Week in the past, it displays the Actual achieved number. 

I have this working they way I would like via a set of measures which give me the Current Week (I can therefore determine if week is in the Past/Current/Future), an Actual Qty and a Plan Qty. See the broken out tables below

1. Plan

2. Actual

3. Actual & Plan

However, what I am looking to do is have a running cumulative total that COMBINES my Actuals to date with my future plans to give me a total number. So in the table above (Table 3. Actual&Plan) it would total what I have actually achieved with what I'm projecting to achieve.

I have made several attempts at this with the closest output being below; 

As you can see this is summing up the Actuals each week, 6 in Week 1, 12 in Week 2 which is a running total of 18 etc. However this stops working when we reach the end of the actuals and doesnt then continue to sum up the Actuals to Date with the remaining Plan. 

The DAX I have used for the above is; 

Note: "CurrentWeekCheck" is bascially looking up a Fiscal Calendar and if the week is in the past, it displays 1, if its a current week or a future week, it displays 0. 

I feel like I'm really close with this, but just cant quite get it over the finish line. If a fresh pair of eyes can help me do a running weekly total of BOTH Actual and Plan, I would be very grateful. 

Thank you. 

[v-rzhou-msft · Accepted solution]

Hi @MiloPowerBI ,

Here I create a sample to have a test.

Measure:

Running Total = 
VAR _CURRENTYEARWEEK =
    YEAR ( TODAY () ) * 100
        + WEEKNUM ( TODAY (), 1 )
VAR _GENERATE =
    GENERATE (
        CALCULATETABLE ( VALUES ( 'Calendar'[YearWeek] ), ALL ( 'Calendar' ) ),
        CALCULATETABLE ( VALUES ( Actual[Product] ), ALL ( Actual[Product] ) )
    )
VAR _ADD =
    ADDCOLUMNS (
        _GENERATE,
        "Value",
            IF (
                'Calendar'[YearWeek] < _CURRENTYEARWEEK,
                CALCULATE (
                    SUM ( Actual[Value] ),
                    FILTER (
                        Actual,
                        Actual[YearWeek] = EARLIER ( [YearWeek] )
                            && Actual[Product] = EARLIER ( [Product] )
                    )
                ) + 0,
                CALCULATE (
                    SUM ( Plan[Value] ),
                    FILTER (
                        Plan,
                        Plan[YearWeek] = EARLIER ( [YearWeek] )
                            && Plan[Product] = EARLIER ( [Product] )
                    )
                )
            )
    )
VAR _MIN =
    CALCULATE ( MIN ( Actual[YearWeek] ), ALLEXCEPT ( Actual, Actual[Product] ) )
VAR _MAX =
    CALCULATE ( MAX ( Plan[YearWeek] ), ALLEXCEPT ( Plan, Plan[Product] ) )
RETURN
    IF (
        MAX ( 'Calendar'[YearWeek] ) >= _MIN
            && MAX ( 'Calendar'[YearWeek] ) <= _MAX,
        SUMX (
            FILTER (
                _ADD,
                [Product] = MAX ( Actual[Product] )
                    && [YearWeek] <= MAX ( 'Calendar'[YearWeek] )
            ),
            [Value]
        )
    )

Result is as below.

Best Regards,
Rico Zhou

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

[MiloPowerBI]

Hello, 

Thank you - this looks very promising. Apologies for the delay in my response, I have been offline for a couple of days. I will try this today and get back to you. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MiloPowerBI]

Hello lbendlin, 

Apologies, I am pretty new around here and didnt realise if I could upload files with data in them. Will take this lesson forward for next time. Thank you for the helpful links.