Calculate Transit time between port destination

[nirrobi]

Hi all,

I have table with the following data:

Index - running number - no special purpose

LineDirection - the line route name

Port - one of the route direction

TransitTime - the time take to go from one port to the one ofter  - e.g. GRPIR to ILASH - 3 days

CumulativeTT - the cummulative time between different port - e.g. TRIST to ILASH - 7 days

I want the user to choose 3port e.g. ILASH and BRSSZ and SGSIN and the PBI should show 56 (32+24),

ILASH to SGSIN  = 32 - 0 = 32

SGSIN to BRSSZ = 33 - 9 = 24

the transhipment happen in SGSING

what is the right dax measure for this case?

hope I was clear.

thanks in advaced !!!

Index	LineDirection	Port	TransitTime	CumulativeTT
1	MD3_E	ILASH	0	0
2	MD3_E	GRPIR	3	3
3	MD3_E	TRIST	4	7
4	MD3_E	TRALI	3	10
5	MD3_E	TRMER	3	13
6	MD3_E	EGSUZ	2	15
7	MD3_E	SAJED	3	18
8	MD3_E	SGSIN	14	32
9	MD3_E	TWKHH	5	37
10	MD3_E	KRPUS	4	41
11	MD3_E	CNSHA	2	43
12	MD3_E	CNNGB	2	45
13	MD3_E	CNYTN	2	47
14	SA3_W	CNSHA	0	0
15	SA3_W	CNNGB	1	1
16	SA3_W	CNYTN	3	4
17	SA3_W	HKHKG	1	5
18	SA3_W	SGSIN	4	9
19	SA3_W	BRIGI	23	32
20	SA3_W	BRSSZ	1	33
21	SA3_W	BRPNG	1	34
22	SA3_W	BRNVT	2	36
23	SA3_W	UYMVD	3	39
24	SA3_W	ARBUE	4	43
25	SA3_W	BRRIG	2	45

[v-chuncz-msft]

@nirrobi,

You may try to use RANKX Function and SUMX Function.

[nirrobi · Accepted solution]

Thanks for your recommendation,

I was able to solved the problem using the following DAX formula:

Cumulative TT=
CALCULATE(
    SUM( Table1[CummulativeTT] ) ,
    FILTER(
        ALL( Table1 ) ,
        Table1[Index] <= MAX( Table1[Index] )
        && Table1[LineDirection] = MAX( Table1[LineDirection] )
    )
)

The *MAGIC* for me wat the last line - where I can compare TEXT with the MAX function.