Complex DAX Operation

[GovindPrajapat]

Hi, I have a live streaming dataset in power bi. The data transformation feature, Add new coloumn and create new table all of this features are disabled for live streaming data. The operation that i want to perform is like that : I have four coloumns in my dataset 1. PlatformId, 2. DateTime, 3. Tag, 4 Value. Now there is a pattern in my data. where the values of Tag are In, Work, EndStart, EndClose and Out sequentially, for this all five records platformId is same and datatime values are in ascending order. next five records are for another platform, then for next platform, then for next platform. I have total 5 platforms. After 5th platform it again starts from first platform and goes till 5th platform, again start from first platform..... and so on. Now i want to find out the difference between In DateTime and Out DateTime, Difference between Work DateTime and EndStart DateTime, Difference between EndStart DateTime and EndClose DateTime. How Can i do it. I dont have index coloumn in my dataset?

[Sahir_Maharaj]

Hello @GovindPrajapat,

Since you don't have an index column and the data transformation features are disabled for live streaming data, you can achieve your desired calculations using DAX measures instead.

1. Create Measures

1.1. Difference between In DateTime and Out DateTime

In_Out_Difference = 
VAR CurrentDateTime = MIN('YourTable'[DateTime])
VAR NextDateTime = CALCULATE(MIN('YourTable'[DateTime]), 'YourTable'[DateTime] > CurrentDateTime, 'YourTable'[Tag] = "Out", 'YourTable'[PlatformId] = EARLIER('YourTable'[PlatformId]))
RETURN
IF('YourTable'[Tag] = "In" && NOT(ISBLANK(NextDateTime)), NextDateTime - CurrentDateTime)

1.2. Difference between Work DateTime and EndStart DateTime

Work_EndStart_Difference = 
VAR CurrentDateTime = MIN('YourTable'[DateTime])
VAR NextDateTime = CALCULATE(MIN('YourTable'[DateTime]), 'YourTable'[DateTime] > CurrentDateTime, 'YourTable'[Tag] = "EndStart", 'YourTable'[PlatformId] = EARLIER('YourTable'[PlatformId]))
RETURN
IF('YourTable'[Tag] = "Work" && NOT(ISBLANK(NextDateTime)), NextDateTime - CurrentDateTime)

1.3. Difference between EndStart DateTime and EndClose DateTime

EndStart_EndClose_Difference = 
VAR CurrentDateTime = MIN('YourTable'[DateTime])
VAR NextDateTime = CALCULATE(MIN('YourTable'[DateTime]), 'YourTable'[DateTime] > CurrentDateTime, 'YourTable'[Tag] = "EndClose", 'YourTable'[PlatformId] = EARLIER('YourTable'[PlatformId]))
RETURN
IF('YourTable'[Tag] = "EndStart" && NOT(ISBLANK(NextDateTime)), NextDateTime - CurrentDateTime)

2. Create visuals using these measures to display the time differences for each platform based on the "Tag" values.

Should you require further assistance please do not hesitate to reach out to me.