Measure with datediff of dates in same column and criteria

[eacy]

Hi,

I am trying to create a measure which will show the delta time between two dates in a column.

If the rows have same ID and the LEVEL is blank, it should show the time between the current row and the previous row which meet the criteria.

Any sugestions ?

[eacy]

I succeded an now my Datediff give me what I want.

Earlier = 
IF(ISBLANK('Table'[LEVEL]);
	DATEDIFF(CALCULATE(MAX('Table'[CREATED]);
		FILTER(ALL('Table');
			'Table'[ID] = EARLIER('Table'[ID]) && ISBLANK('Table'[LEVEL]) && 'Table'[CREATED] < EARLIER('Table'[CREATED])
		)
	);'Table'[CREATED];MINUTE);
BLANK()
)

This is obvious just a test table, in my real table the "Table[ID]" is related to an GroupID in another table (one to many) where the column "Field" with the same "GroupID" is equal to "assignee"

Now I would like to add a criteria saying: Only do DATEDIFF for rows where "Table[LEVEL] is blank AND related "Field" column contain the value "assignee"

[eacy]

[austinsense]

var currentid = Table[ID]
var currentdate = Table[Created]

return
IF (currentid = 1, BLANK(),
      currentdate-
      CALCULATE (MAX (Table[Created]),
            FILTER(Table, Table[ID] = currentid && Table[ID] < currentdate)
      )
)

I love this question - calculated columns get tricky when we're doing a calculation in one row and we have to access the contents of other rows.  The pattern here is to use variables (var ... return) and FILTER

[eacy]

Hi austinsense,

This looks very interesting I have newer tried to work with variable in DAX.

But the result is not quiet what I expected. Let me try to refraise.

I would like to have a column containing the amount of time from the date in the current CREATED column until the date in the CREATED column in where [ID]=[ID] && ISBLANK(Table[LEVEL]).

I have sort of been able to do that with calculated columns by using EARLIER, there I could get the time between each row but not jump the rows which were not blank.

An I would like to use measure instead.

The new measure should show 24 hours (or any other unit) in row 3 considering that the CREATED date in row 2 and 3 is blank and they belong to the same ID

Again it should show 72 hours in row 9 because that is the time between CREATED date in row 6 until row 9 which is the next blank LEVEL with ID 2.

Row 10 should show 24 hours.

I appriciate any feedback, thanks

[austinsense]

It was early in the morning and I didn't write this correctly the first time

//For the calculated column
var currentid = Table[ID]
var currentlevel= Table[Level]

return
IF (currentlevel= 1, BLANK(),
      currentdate-
      CALCULATE (MIN(Table[Created]),
            FILTER(Table, currentlevel<>1, Table[ID] = currentid)
      )
)

I can help you write this as a measure but I'm tied up for a while - maybe someone else could jump in and help out?

[eacy]

Hi,

I got a little closer, but not yet there.

Right now I am using MIN('Table'[CREATED]) as my first date in DATEDIFF but it supposed to be "PREVIOUS" date if the previous line meet my criterium.

I expect I have to use EARLIER somehow? or do anyone have any suggestions how to get a result where line number three will become1440 minutes (like line number two in this example) and not the difference from the first line that meet my criterium.

If anybody can do this in a measure it would be great.

Between = 
var currentid = 'Table'[ID] 
var currentlevel= 'Table'[LEVEL]
var currentdate= 'Table'[CREATED] 
return

IF(ISBLANK(currentlevel);
      DATEDIFF(CALCULATE 
            (MIN('Table'[CREATED]);
            FILTER('Table'; ISBLANK(currentlevel) && 'Table'[ID] = currentid));
            currentdate; MINUTE);
      BLANK()
)

Appreciate any suggestions

[eacy]

forgot to upload a picture...

[Meraki_jeet]

Hi @eacy & @ImkeF  & @austinsense ,

  I have similar problem, could someone please help me out with DAX for below as I am stuck in middle. Below is my sample data

                      Resource Name                                  DIS_scheduled                       Scan Time
TEST_CONCURRENT_SCANS_ORA_SM_8       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_7       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_6       SM_PROFILING_PRD           12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_5       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_4       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_3       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_2       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_1       SM_PROFILING_PRD           12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_MD_5      MD_PROFILING_PRD           12/27/2023 12:00
TEST_CONCURRENT_SCANS_ORA_MD_4      MD_PROFILING_PRD           12/27/2023 12:00
TEST_CONCURRENT_SCANS_ORA_MD_3      MD_PROFILING_PRD           12/27/2023 13:30
TEST_CONCURRENT_SCANS_ORA_MD_2      MD_PROFILING_PRD           12/27/2023 15:00
TEST_CONCURRENT_SCANS_ORA_MD_1      MD_PROFILING_PRD           12/27/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_3       LG_PROFILING_PRD             12/28/2023 13:00
TEST_CONCURRENT_SCANS_ORA_LG_2      LG_PROFILING_PRD              12/28/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_1      LG_PROFILING_PRD              12/28/2023 9:00
TEST_CONCURRENT_SCANS_ORA_XL_2       XL_PROFILING_PRD              12/29/2023 10:00
TEST_CONCURRENT_SCANS_ORA_XL_1       XL_PROFILING_PRD              12/30/2023 7:00
TEST_CONCURRENT_SCANS_ORA_SM_8     SM_PROFILING_PRD             12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_7     SM_PROFILING_PRD             12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_6     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_5     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_4     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_3     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_2     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_1     SM_PROFILING_PRD            12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_MD_5     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_4     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_3     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_2     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_1     MD_PROFILING_PRD          12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_3      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_LG_2      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_LG_1      LG_PROFILING_PRD            12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_XL_2       XL_PROFILING_PRD            12/31/2023 7:00
TEST_CONCURRENT_SCANS_ORA_XL_1       XL_PROFILING_PRD            12/31/2023 7:00

so here 

>> any resource should not be scheduled(i.e. Scan_time column above) if there are resources already scheduled upto 2 hrs before for DIS_scheduled = "SM_PROFILING_PRD"

>> any resource should not be scheduled if there are resources already scheduled upto 5 hrs before for DIS_scheduled = "MD_PROFILING_PRD"

>> any resource should not be scheduled if there are resources already scheduled upto 10 hrs before for DIS_scheduled = "LG_PROFILING_PRD"
>> any resource should not be scheduled if there are resources already scheduled upto 24 hrs before for DIS_scheduled = "XL_PROFILING_PRD"

So for Example, For DIS_scheduled = "SM_PROFILING_PRD", if  2 Resources have Scan_time on 12/31/2023 14:30 but there are already  resources having Scan_time at 12/31/2023 13:00 which is an issue as no resource should be scheduled 2hrs before

I have to use Scan_Time column to check above conditions and create new column with output as "Issue" or "No issue"
Please let me know if more data is required.

Thanks 

[ImkeF]

This is the M-code that would do the trick in the query editor:

let
    Source = YourImportStep,
    ChgType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"LEVEL", type text}, {"CREATED", type datetime}}),
    AddIndex = Table.AddIndexColumn(ChgType, "Index", 1, 1),
    FilterBlankRows = Table.SelectRows(AddIndex, each ([LEVEL] = "")),
    AddIndexFilter = Table.AddIndexColumn(FilterBlankRows, "IndexFilter", 0, 1),
    Durations = Table.AddColumn(AddIndexFilter, "Duration", each try if [ID]=AddIndexFilter[ID]{[IndexFilter]-1} then ([CREATED]-AddIndexFilter[CREATED]{[IndexFilter]-1})*24*60 else null otherwise null),
    DurationsMin = Table.TransformColumnTypes(Durations,{{"Duration", type number}}),
    MergeBoth = Table.NestedJoin(AddIndex,{"Index"},DurationsMin,{"Index"},"NewColumn",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(MergeBoth, "NewColumn", {"Duration"}, {"Duration"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}})
in
    Sort

LinkToFile