Forum Discussion

datadonuts's avatar
datadonuts
Icon for Advocate II rankAdvocate II
5 years ago

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],
[Total Budget adjusted]
)
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=eyJrIjoiNzY2MDBlNGYtZWQzZC00MGM3LWI0ZWItMDk0NmMzZTFlZjgyIiwidCI6ImEwZTY5OTBhLTk5ZTctNDU2OC1hYWY4LWQxZDBkMTUwNzEzMiIsImMiOjl9" frameborder="0" allowFullScreen="true"></iframe> 

 

 

 

 

 

 

1 Reply

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