Forum Discussion
Difference between values in a day and incremental column
- 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.
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?
- Anonymous2 years agoNot 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.