cancel
Showing results for
Did you mean:
Helper II

## Following the ITALIANS, but wrong RUNNING TOTAL with TOTALYTD

I just followed a video from Alberto "Showing actuals and forecasts in the same chart with Power BI" (https://youtu.be/DKgF-5QHY68) as well their blog here -> https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/

I replicated successfully the example and wanted bring it a step further to my example.

Unfortunately I got stuck computing the RUNNING TOTAL.

First what I want to do: In month 5 (May) the last sales were made on e.g. May 15. In this case I want to show the Budget value instead of Sales. From there I want to show only the budget value from June to December. This also as a running total.

I achieve that with

RB2 Remaining Budget =
VAR RB_lastsalesdate = CALCULATE(MAX(Orders1[OrderDate]), REMOVEFILTERS(Dates))
VAR RB_datecheck = IF(RB_lastsalesdate <= ENDOFMONTH(Dates[Date]), 1, 0)
VAR RB_budgetafter = IF(
RB_datecheck = 0,
[Total Sales],
)
RETURN
RB_budgetafter

Success. Here we go!

So far so good. This gives me a column that shows the TotalSales from Jan to April followed by the Budget from May to December.

Now calculation the RUNNING TOTAL (RB2 cumulated) using TOTALYTD, as described in their blog. Unfortunatey I got a wrong result. The number 423,768 in 2008-5 is the sum of the budget from 1 - 5. From 2008-6 to 2008-12 it adds up the budget number as required, but on the wrong basis number from 2008-5; so actually the running total is wrongly calculated from June to December.

The columns "Testing", "last sales date all" and "last date month" are just for understanding.

I was not able to figure out, where the problem is neither to find a replacement for the TOTALYTD running total calculation.

I guess, it can only achieved it with a "virtual table" holding the figures of the column "Remaining Budget" and then compute a running total.

Ideas are hightly appreciated.

Note: the sales granularity is on the dat while the Budget is monthly.

<iframe width="600" height="373.5" src="https://app.powerbi.com/view?r=eyJrIjoiNzY2MDBlNGYtZWQzZC00M...

Super User

RB2 cumulated should be computable as a SUMX() of RB2 Remaining Budget.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors