Sum exclude blanks

[Anonymous]

Hi all,

I'm trying to figure out a way to make this work;

Every day a person works he will have to do multiple calls. On each call the employee is promoted to choose if it's the first visit of the day, the visit in between or the last visit of the day. If it's the first visit of the day the employee is obligated to fill in their odometer gain and when it's the last visit of the day aswel. 

So the data would look like this:

Date & Time	User	Begin odometer	End odometer gain
03-06-2020 08:00 am	Tim	14562	null
03-06-2020 09:00 am	Tim	null	null
03-06-2020 11:00 am	Tim	null	null
03-06-2020 12:00 am	Tim	null	14685

The sum would be: 14685 - 14562 = 123. This works perfectly. But if the employee is not yet done with his day the current sum will give me an negative number as show in the picture below. Is there anway to figure this out?

Thanks in advance!

Wrong                                                                            Good

KmVertrek minus KmAankomst = 
IF (
    NOT ISBLANK ( MAX('datalake answers'[KmVertrek]) ),
    ( MIN('datalake answers'[KmAankomst]) - MAX('datalake answers'[KmVertrek]) ) 
)

[amitchandak · Accepted solution]

@Anonymous , try like

sumx(filter(summarize(Table, Table[Date & Time].Date, Table[User], "_1" ,sumx(Table, Table[Begin odometer]-Table[End odometer gain])),[_1]>0),[_1])

Group at day and user level and then check >0

[Anonymous]

Hi Amit,

Thanks, i got it working using this formula:

KmVertrek minus KmAankomst = 
sumx(filter(summarize('datalake calls', 'datalake calls'[ActualStart].[Date], 'datalake calls'[UserId], "_1" ,sumx('datalake answers', 'datalake answers'[KmAankomst] - 'datalake answers'[KmVertrek])),[_1]>0),[_1])