Forum Discussion
Filter data and subtract
Hi comunity,
I've been working on power BI for some time now, and I like it very much, I have a problem with a function, maybe you can help me, I want to know the difference per user of the use dsize between the last two dates. El resultado lo muestre en otra tabla, con el nombre de usuario y la diferencia
I appreciate your help.
HI Anonymous,
I try to extract datetime string from your source name column, but I found some records contains incorrect time value.
Calculate column formula:
DateTime characters = VAR trimSpace = SUBSTITUTE ( UPPER ( [Source.Name] ), " ", "" ) VAR removeUnder = SUBSTITUTE ( trimSpace, "_", "" ) RETURN PATHITEM ( SUBSTITUTE ( removeUnder, "LOCAL", "|" ), 1 )I'd like to know last four characters means time value or just id? If it means time value, please share some correct sampel data here for test.
Sample:
Use calculated column to get date value from above text.
Date = VAR _date = LEFT ( [DateTime characters], 8 ) VAR _year = LEFT ( _date, 4 ) VAR _month = RIGHT ( LEFT ( _date, 6 ), 2 ) VAR _day = RIGHT ( _date, 2 ) RETURN DATE ( _year, _month, _day )Measure of diff between previous and current:
Diff of previous = VAR previous = MAXX ( FILTER ( ALL ( 'sample' ), [Users] = SELECTEDVALUE ( 'sample'[Users] ) && [Date] < MAX ( 'sample'[Date] ) ), [Date] ) RETURN IF ( previous <> BLANK (), DATEDIFF ( previous, MAX ( 'sample'[Date] ), DAY ), 0 )Result:
Regards,
Xiaoxin Sheng
2 Replies
HI Anonymous,
I try to extract datetime string from your source name column, but I found some records contains incorrect time value.
Calculate column formula:
DateTime characters = VAR trimSpace = SUBSTITUTE ( UPPER ( [Source.Name] ), " ", "" ) VAR removeUnder = SUBSTITUTE ( trimSpace, "_", "" ) RETURN PATHITEM ( SUBSTITUTE ( removeUnder, "LOCAL", "|" ), 1 )I'd like to know last four characters means time value or just id? If it means time value, please share some correct sampel data here for test.
Sample:
Use calculated column to get date value from above text.
Date = VAR _date = LEFT ( [DateTime characters], 8 ) VAR _year = LEFT ( _date, 4 ) VAR _month = RIGHT ( LEFT ( _date, 6 ), 2 ) VAR _day = RIGHT ( _date, 2 ) RETURN DATE ( _year, _month, _day )Measure of diff between previous and current:
Diff of previous = VAR previous = MAXX ( FILTER ( ALL ( 'sample' ), [Users] = SELECTEDVALUE ( 'sample'[Users] ) && [Date] < MAX ( 'sample'[Date] ) ), [Date] ) RETURN IF ( previous <> BLANK (), DATEDIFF ( previous, MAX ( 'sample'[Date] ), DAY ), 0 )Result:
Regards,
Xiaoxin Sheng