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.
- LorenzoTr2 years agoFrequent 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?
- 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.