Forum Discussion
Solved
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"...
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.
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
