Filter data and subtract

[Anonymous]

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.

[v-shex-msft · Accepted solution]

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

[Anonymous]

Hi

Thank you for your answer. The difference is for the "Used Size (MB)" column only for the two most recent dates, no time is required.