Forum Discussion
Sum exclude blanks
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]) )
)
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
2 Replies
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