Forum Discussion
Difference between values in a day and incremental column
Hi All,
I would calculate the difference between values in a day and relative column that shows that increment of the day (values at 12AM should be 0).
Here the tab with columns "Diff" and "Increm" that are what I need.
Thanks!
Lorenzo
| Timestamp_Convert | Count_H | Diff | Increm |
| 16/5/24 1.00 | 20 | - | - |
| 16/5/24 2.00 | 22 | 2 | 2 |
| 16/5/24 3.00 | 25 | 3 | 5 |
| 16/5/24 4.00 | 32 | 7 | 12 |
| 16/5/24 5.00 | 33 | 1 | 13 |
| 16/5/24 6.00 | 33 | 0 | 13 |
| 16/5/24 7.00 | 35 | 2 | 15 |
| 16/5/24 8.00 | 36 | 1 | 16 |
| 16/5/24 9.00 | 39 | 3 | 19 |
| 16/5/24 10.00 | 41 | 2 | 21 |
| 16/5/24 11.00 | 43 | 2 | 23 |
| 16/5/24 12.00 | 44 | 1 | 24 |
| 16/5/24 13.00 | 45 | 1 | 25 |
| 16/5/24 14.00 | 47 | 2 | 27 |
| 16/5/24 15.00 | 50 | 3 | 30 |
| 16/5/24 16.00 | 54 | 4 | 34 |
| 16/5/24 17.00 | 56 | 2 | 36 |
| 16/5/24 18.00 | 57 | 1 | 37 |
| 16/5/24 19.00 | 60 | 3 | 40 |
| 16/5/24 20.00 | 62 | 2 | 42 |
| 16/5/24 21.00 | 63 | 1 | 43 |
| 16/5/24 22.00 | 64 | 1 | 44 |
| 16/5/24 23.00 | 66 | 2 | 46 |
| 17/5/24 0.00 | 67 | 1 | 0 |
| 19/5/24 1.00 | 68 | 1 | 1 |
| 20/5/24 2.00 | 72 | 4 | 5 |
| 21/5/24 3.00 | 73 | 1 | 6 |
| 22/5/24 4.00 | 79 | 6 | 12 |
- Anonymous2 years ago
Hi LorenzoTr ,
You want to make sure that your date column is in the correct time format, such as "YYYY-MM-DD HH:MM:SS .
My diff's DAX has the EARLIER function, which is used in the calculated column, so be careful to recognize it.
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- AnonymousNot applicable
Hi LorenzoTr ,
Thank you fahadqadir3 and amitchandak very much for the solution, and I've tried other ways to help you understand the problem:These are the two solutions I wrote in the case of accomplishing your first goal, one in MEASUREMENT and one in CALCULATED COLUMNS.
diff is the solution to your second need.Column = VAR now_='Table'[Count_H] VAR pervious= CALCULATE(MAX('Table'[Count_H]),FILTER('Table', [Timestamp_Convert]<EARLIER([Timestamp_Convert]) ) ) RETURN IF(pervious =BLANK(), BLANK(),now_-pervious)diff 1 = VAR currenttime = MAX('Table'[Timestamp_Convert]) VAR previoustime=CALCULATE(MAX('Table'[Timestamp_Convert]),FILTER(ALLSELECTED('Table'),'Table'[Timestamp_Convert]<currenttime) ) VAR previousvalue=CALCULATE(MAX('Table'[Count_H]),FILTER(ALLSELECTED('Table'),'Table'[Timestamp_Convert]=previoustime) ) RETURN IF( currenttime=MINX(ALL('Table'),'Table'[Timestamp_Convert]), 0, MAX('Table'[Count_H])-previousvalue)diff = VAR restTime =TIME(24, 0, 0) VAR currtime =TIME(HOUR('Table'[Timestamp_Convert]),MINUTE('Table'[Timestamp_Convert]),SECOND('Table'[Timestamp_Convert])) VAR currentcount='Table'[Count_H] VAR previouscount=CALCULATE(SUM('Table'[Column]),FILTER('Table','Table'[Timestamp_Convert]<=EARLIER('Table'[Timestamp_Convert])&&YEAR('Table'[Timestamp_Convert])=YEAR(EARLIER('Table'[Timestamp_Convert])) ) ) RETURN IF( currtime=restTime, 0, previouscount )I just that you are encountering the error because you are using earlier in DAX in measure, the place where earlier should be used is in calculated columns and virtual tables, the following URL may solve your query.Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- LorenzoTrFrequent Visitor
Thanks, Diff1 is working well!
About diff1, I tried to enter it as a measure but I find some issues with formulas (HOUR, MINUTE, SECONDS, EARLIER) cause it's not possible to enter column (ex:
HOUR('Table'[Timestamp_Convert]Do you've any suggestion?
- AnonymousNot applicable
Hi LorenzoTr ,
You want to make sure that your date column is in the correct time format, such as "YYYY-MM-DD HH:MM:SS .
My diff's DAX has the EARLIER function, which is used in the calculated column, so be careful to recognize it.
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- amitchandak
Super User
LorenzoTr , Two New columns
Create an additional Date column in Table
Date = datevalue([Timestamp_Convert])
and then
Diff with last value =
var _min = Minx(filter(Table, [Date]= Earlier([Date])), [Timestamp_Convert])
return
Minx(filter(Table, [Date]= Earlier([Date]) && [Timestamp_Convert] = _min), [Count_H] ) - [Count_H] - fahadqadir3
Solution Supplier
Differsa =
VAR CurrentRow = 'Table'[Count_H]
VAR PreviousRow =
CALCULATE(
MAX('Table'[Count_H]),
FILTER(
ALL('Table'),
'Table'[Timestamp_Convert] < EARLIER('Table'[Timestamp_Convert])
)
)
RETURN
IF(ISBLANK(PreviousRow), BLANK(), CurrentRow - PreviousRow)Increment =
VAR CurrentTimestamp = 'Table'[Timestamp_Convert]
VAR StartOfDay =
CALCULATE(
MIN('Table'[Timestamp_Convert]),
FILTER(
'Table',
FORMAT('Table'[Timestamp_Convert], "yyyy-MM-dd") = FORMAT(CurrentTimestamp, "yyyy-MM-dd")
)
)
RETURN
IF(
'Table'[Count_H] = CALCULATE(MIN('Table'[Count_H]), 'Table'[Timestamp_Convert] = StartOfDay),
0,
CALCULATE(
SUMX(
FILTER(
'Table',
'Table'[Timestamp_Convert] <= CurrentTimestamp &&
FORMAT('Table'[Timestamp_Convert], "yyyy-MM-dd") = FORMAT(CurrentTimestamp, "yyyy-MM-dd")
),
'Table'[Differsa] // Use 'Differsa' as it's the name of your calculated column
),
FILTER(
ALL('Table'),
'Table'[Timestamp_Convert] <= CurrentTimestamp &&
FORMAT('Table'[Timestamp_Convert], "yyyy-MM-dd") = FORMAT(CurrentTimestamp, "yyyy-MM-dd")
)
)
)LorenzoTr Hope it works.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!- LorenzoTrFrequent Visitor
Thanks for the answer. Cannot undertand why I've an error when I enter DAX function EARLIER.
Is there any alternative to this? thanks