The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Solved! Go to Solution.
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 Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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
)
Hope it helps!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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?
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 Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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!!
Thanks for the answer. Cannot undertand why I've an error when I enter DAX function EARLIER.
Is there any alternative to this? thanks
@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]