Calculation of Running Total with CALCULATE() and a Date filter with none unique Dates

[iamstrippy]

Hello,
i've got a problem with the calculation of a running total (actually two running totals) with a filter for the specific date and a filter which filters if a certain status of the enrty is met or not. 
I've added a screenshot of the formula to compute the result and some sample data to better understand the issue.
The problem basically is that on the same date the same amount of "Kosten" (price) can occur multiple times and the CALCULATE()-Function then adds all those same values on the same day together. I've tried fixing the this error by using a IF-Function with HASONEVALUE and DISTINCT but i am not sure if this is actually a good way to solve the problem. 

In the corresponding column which i created to display the measure the values now seem to be right but unfortunately now when i try to use the running total in my report in a stacked area chart with other computed running totals i get very few data points and no real running total (pink graph in the stacked area chart). The source data contains more than 1000+ values but only a few are shown in the graph.

Thanks in advance for helping me with this problem.

Best regards
Mario

[selimovd]

Hey @iamstrippy ,

your request is not very clear.

So you want to add only distinct values of kosten per day? For example if on 17.06.2021 there are 2 rows with kosten of 2,99 the sum should be 2,99 instead of 5,98. Is that right? If yes are there any other columns involved or only date and the kosten?

The graphic also doesn't really help as I don't know what I'm looking at.

It would also help if you could provide a file or at least the tables and formulas. This makes it easier to help you.

Otherwise I have to type everything from a screenshot in order to reproduce your case....

Best regards

Denis

[iamstrippy]

Hey @selimovd,
thanks for your quick answer. I am sorry that my request is not entirely clear.

I want to calculate the running total for every entry in the dataset (every row). I only used the DISTINCT Function because otherwise the measure calculates the values the wrong way. 

For example as shown in the screenshot on the 23.03.2021 there are three different entries with a price ("Kosten") of 36,45 and the measure then proceeds to add those daily same values together to 36,45*3 and displays the result in every row in the V2-Column in which 36,45 originally occured. 
Thats why i used the DISTINCT-Fuction but i don't know if this is/was the right way to solve my problem but it at least produces the right values in the V2-Display column.

The target visualisation i am trying to rebuild out of an excel sheet looks like this:

The brown line i marked with the arrow is what i would like to achieve with this function (for the past with one measure and as a forecast as with the second one).

I've uploaded the PowerBi file with all sensitive data removed - i hope this makes it easier for you.
https://1drv.ms/u/s!Av-f2ZPF426XgcctqXSWe24NIE1AQQ?e=wGskdM

Thanks a lot 

Best regards
Mario

[v-kkf-msft]

Hi @iamstrippy ,

What is your expected output? You said that you get very few data points, but I see that there are only about 600 rows of data in your Auftragsübersicht table and no 1000+ values are found.

What does "running total" mean ? What is it related to? How to calculate?

Best Regards,
Winniz

[iamstrippy]

Hi Winniz,
i am trying to calculate the total costs of a project split up in different categories. Additionally i want to show some kind of forecast of the costs to come in relationship to the current date (the brown line in the excel visualization after the red "today" vertical line). The brown graph is the one i am struggling to calculate.

And yes you are right there are less rows of usable data then i initially thought but nothernless i am only getting less then 50 data points and not all of the available 600.

Running total means the ongoing cumulative calculation of all the different categories of costs in my case. This is represented through the coloured areas in the stacked area chart. It is related to the individual costs of the each category, the current date in the calendar in comparison to different dates when new entries were added to the data source (increase in the costs of certain categories so to speak) and a status indicator which tells the user if the job is completed or not. Currently i am calculating the running total via the following formula:

V-Ist2-M = 
VAR heute=TODAY()
VAR result =
    CALCULATE(
        SUM('Auftragsübersicht'[Kosten]),
            'Auftragsübersicht'[Terminierter Start]<heute,
            FILTER('Auftragsübersicht','Auftragsübersicht'[Statustext]="beendet"),
            FILTER('Auftragsübersicht',
                IF(HASONEVALUE('Auftragsübersicht'[Kosten]),
              DISTINCT('Auftragsübersicht'[Kosten]))),
            ALLEXCEPT('Auftragsübersicht','Auftragsübersicht'[Kosten],'Auftragsübersicht'[Statustext],'Auftragsübersicht'[Terminierter Start])
    )
Return
    result

The Power Bi File is also available under the link i posted earlier if you want to check it out.

Thanks a lot and best regards
Mario

[v-kkf-msft]

Hi @iamstrippy ，

I filter out the data in V2 that is not blank, then distinctcount the Terminierter Start column and get 93. That is, it will only display up to 93 data points in the area chart.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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