Calculate number of days in use

[Ratax]

hello

So i want DaysBetweenLoad to calculate the number of days between loads with regards to each system.

Take system 1 for example: The first Item is loaded the 01-12 which is the first so no days since last item was loaded. The next item on system 1 is loaded 03-12 which is 2 days after the first and so furth.

The rank should count what chronological order the item was loaded with regards to system and date.

So the item loaded on system 1 the 01-12 is the first, and the item loaded 03-12 is the second. 

Item	LoadedDate	SystemID	DaysBetweenLastLoad	Rank with regards to date and system
554	01-12-2020	1	-	1
446	03-12-2020	1	2	2
225	06-12-2020	2	-	1
112	05-12-2020	3	-	1
114	08-12-2020	1	3	4
665	19-12-2020	2	13	2
113	12-12-2020	3	7	2
887	05-12-2020	1	2	2

i hope this clarifies things

Best Regards

[v-easonf-msft · Accepted solution]

Hi , @Ratax 

Try calculated column as below:

DaysBetweenLastLoad = 
VAR Last_loadedDate =
    CALCULATE (
        MAX ( 'Table'[LoadedDate] ),
        'Table',
        'Table'[SystemID] = EARLIER ( 'Table'[SystemID] ),
        'Table'[LoadedDate] < EARLIER ( 'Table'[LoadedDate] )
    )
RETURN
    IF (
        ISBLANK ( Last_loadedDate ),
        BLANK (),
        DATEDIFF ( Last_loadedDate, 'Table'[LoadedDate], DAY )
    )

Rank with regards to date and system =
RANKX (
    FILTER ( 'Table', 'Table'[SystemID] = EARLIER ( 'Table'[SystemID] ) ),
    'Table'[LoadedDate],
    ,
    ASC,
    DENSE
)

 

Please check sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

If you want the number of days on which each item was used, you could just have a measure of

Days Used = DISTINCTCOUNT(Table[CreationDateTime])

If you want to know the span of days (first to last date), you could use

Days Used = DATEDIFF(MIN(Table[CreationDateTime]), MAX(Table[CreationDateTime]), DAY)

Pat

[VijayP]

@Ratax 

Question is not clear 

What is the definition of Item? is the system? if you could share some sample data with required result with that sample, solution will be much easier to know

Vijay Perepa

[Ratax]

i updated the post, hope it helps