topic Running total not working when there is 0 in any row in DAX Commands and Tips

Running total not working when there is 0 in any row

Anonymous — Tue, 11 Feb 2020 23:43:35 GMT

I've created a running total to calculate the running total of tasks completed each day. It seems to be working ok if the value in the field is not 0, however when the value is 0 it just shows 0 rather than the running total from the previous day. Also I am not sure why it is displaying the same date multiple times. The screenshot below

The measure I used below

RUNNINGTOTALACTUAL = CALCULATE(

SUM('Tasks'[ActualFinishDateNumber-For S-curve only]),

FILTER(

ALLSELECTED(Tasks),

Tasks[TaskActualFinishDate] <= MAX(Tasks[TaskActualFinishDate])

)

Any help would be much appreciated. Thanks

Re: Running total not working when there is 0 in any row

Stachu — Wed, 19 Feb 2020 08:39:36 GMT

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

it may be related that you use Tasks[TaskActualFinishDate] in the formula and show [TaskFinishDate] in the visual, but without looking at the data it's really hard to judge

Re: Running total not working when there is 0 in any row

Anonymous — Wed, 15 Apr 2020 13:58:27 GMT

Thanks for you help I think that was the reason i've resolved that issue by changing the field from Actual Finish date to Finish Date now all fields are referencing the same date field and seems to be working ok. However I have now another issue with slightly different measure The data looks like this:

TASKS TABLE

TASK NAME	FINISH DATE	BASELINE FINISH DATE	ACTUAL FINISH DATE	VALUE
A	12/01/2020	03/01/2020	12/01/2020	£100
B	13/01/2020	04/01/2020	13/01/2020	£50
C	14/01/2020	05/01/2020	14/01/2020	£20
D	15/01/2020	06/01/2020		£10
E	16/01/2020	07/01/2020		£10

I want to have S curve with 3 lines:

Forecast Running total ( Sum of Value based on Finish dates over time) ,

Baseline RUnning total ( Sum of Value based on Baseline finish dates over time) ,

Actual RUnning total ( Sum of Value based on Actual finish dates over time) ,

Measures used are same for all 3 lines just have different Value fields.It seems to be working however Baseline and forecast lines are identical even though they should not be based on the values and I cannot workout why any thoughts?

RUNNNINGTOTALFINANCEFORECAST =

CALCULATE(

SUM(Tasks[ForecastValue]), #Created a new conditional collumn from VALUE field logic used: IF Finish date not null, Tasks[VALUE], 0

FILTER(

ALLSELECTED(Tasks),

Tasks[TaskFinishDate] <= MAX(Tasks[TaskFinishDate])

)

Re: Running total not working when there is 0 in any row

Stachu — Mon, 20 Apr 2020 04:59:14 GMT

your visual is using only one date of the 3 fields in the axis either the regular finish date or baseline, or actual

in order for this to work you need a separate calendar table with active join with the date field you will use the most, and inactive joins with the rest

you can then activate the inactive joins in the related formula using USERRELATIONSHIP

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

Re: Running total not working when there is 0 in any row

Anonymous — Mon, 27 Apr 2020 09:50:03 GMT

Thank you 🙂