Forum Discussion

MiloPowerBI's avatar
MiloPowerBI
Frequent Visitor
3 years ago
Solved

Running Weekly Total Combining Two Data Sources

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. 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

4 Replies

    • MiloPowerBI's avatar
      MiloPowerBI
      Frequent Visitor
      3 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      MiloPowerBI
      Frequent Visitor
      3 years ago

      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.