Create Date column based on filters

[avpk729]

Hi Experts, hope you are doing well.

I have been struggling with the below issue for a couple of hours now and am unable to find any previous question similar to this one. If perhaps a solved question already exists, please help me find it.

My objective:

I have a date value for every "GeoSite_cd", "Shipment_No" and "Subevent" combination. That means, for a 'GeoSite_cd' = 'NYLYTPT' I have a 'Shipment_no' = '207075774' which has two dates: 'DISCHARGE-TRF' = "5/6/2021" and a 'Load-TRF' = "12/06/2021"

I want to basically find the difference between the "Load-TRF" date and the "Discharge-TRF" date. But inorder to do that I need to create two columns with the respective data. 

The screenshot above shows the data in "Table" Visual, and the image below is showing me the data in "Matrix" visual:

I want to achieve above in a "Table" visual so that I can perform the date subtraction between "Load-TRF" and "Discharge-TRF"

Hope the wise ones here can help me out. 

Cheers

Pk

P.s Sample Data:

Shipment No Booked_FFE GeoSite_Cd Subevent Earliest Operational_TS
207075774 1 MYTPPTM DISCHARG-TRF 25/6/2021 7:00
207075774 1 MYTPPTM LOAD-TRF 18/6/2021 1:00
207075774 1 NZLYTPT DISCHARG-TRF 5/6/2021 14:00
207075774 1 NZLYTPT LOAD-TRF 12/6/2021 2:00
207361654 1 MYTPPTM DISCHARG-TRF 31/5/2021 16:30
207361654 1 MYTPPTM LOAD-TRF 7/6/2021 5:30
207361654 1 SGSINPS DISCHARG-TRF 7/6/2021 11:11
207361654 1 SGSINPS LOAD-TRF 14/6/2021 17:00
207385608 1 SGSINPS LOAD-TRF 6/6/2021 0:00
207385651 1 SGSINPS LOAD-TRF 6/6/2021 0:00
207820165 1 MYTPPTM LOAD-TRF 20/6/2021 19:00
208264330 1 MYTPPTM LOAD-TRF 4/6/2021 2:00
208269653 1 SGSINPS DISCHARG-TRF 18/6/2021 16:00
208269653 1 SGSINPS LOAD-TRF 22/6/2021 4:00
208269692 1 MYTPPTM DISCHARG-TRF 26/6/2021 6:00

[v-easonf-msft · Accepted solution]

Hi, @avpk729 

Try measure as below:

Date_DISCHARG-TRF = 
CALCULATE (
    MAX ( 'Table'[Operational_TS] ),
    ALLEXCEPT ( 'Table', 'Table'[Shipment No], 'Table'[GeoSite_Cd] ),
    'Table'[Subevent] = "DISCHARG-TRF"
)

Date_LOAD-TRF = 
CALCULATE (
    MAX ( 'Table'[Operational_TS] ),
    ALLEXCEPT ( 'Table', 'Table'[Shipment No], 'Table'[GeoSite_Cd] ),
    'Table'[Subevent] = "LOAD-TRF"
)

date subtraction = 
DATEDIFF ( [Date_DISCHARG-TRF], [Date_LOAD-TRF], DAY )

Please check my attached pbix 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.

[avpk729]

Dwell = SUMX('E2E tA_E2EServiceDelivery_CombAgg', DATEDIFF(CALCULATE(MIN('E2E tA_E2EServiceDelivery_CombAgg'[Operational_TS],FILTER('E2E tA_E2EServiceDelivery_CombAgg','E2E tA_E2EServiceDelivery_CombAgg'[Subevent_Cd]="DISCHARGE-TRF"))), CALCULATE(MAX('E2E tA_E2EServiceDelivery_CombAgg'[Operational_TS],FILTER('E2E tA_E2EServiceDelivery_CombAgg','E2E tA_E2EServiceDelivery_CombAgg'[Subevent_Cd]="LOAD-TRF"))),DAY))

I tried like above also, surely I missed something, as it did not give me the 'unexpected parameter' error but the visualisation failed when I tried to add this measure to the report.

[v-easonf-msft · Accepted solution]

Hi, @avpk729 

Try measure as below:

Date_DISCHARG-TRF = 
CALCULATE (
    MAX ( 'Table'[Operational_TS] ),
    ALLEXCEPT ( 'Table', 'Table'[Shipment No], 'Table'[GeoSite_Cd] ),
    'Table'[Subevent] = "DISCHARG-TRF"
)

Date_LOAD-TRF = 
CALCULATE (
    MAX ( 'Table'[Operational_TS] ),
    ALLEXCEPT ( 'Table', 'Table'[Shipment No], 'Table'[GeoSite_Cd] ),
    'Table'[Subevent] = "LOAD-TRF"
)

date subtraction = 
DATEDIFF ( [Date_DISCHARG-TRF], [Date_LOAD-TRF], DAY )

Please check my attached pbix 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.

[avpk729 · Accepted solution]

Dear Eason,

Thank you for the above, the measures worked excellently and am able to get the columns very well.

But when I program the difference column, then the whole table breaks:

I suspect it has something to do with the context filters. The original table in my report has more than 25 columns and not sure which one is conflicting.

So since this query was about creating the correct measure, I believe we have succeeded. Therefore thank you for your help, I will mark this one as solved.

Best regards,

And my sincere thanks as well.

[amitchandak]

@avpk729 , Try a measure like

averageX(Summarize(Table,Table[ID],Table[GeoSite_cd], Table[Shipment_No] "_1", datediff(calculate(Min(Table[Operational_TS]), filter(Table, Table[subevent] = "DISCHARGE-TRF")) ,calculate(Max(Table[Operational_TS]), filter(Table, Table[subevent] = "DISCHARGE-TRF")),day)),[_1])

or

sumx(Summarize(Table,Table[ID],Table[GeoSite_cd], Table[Shipment_No] "_1", datediff(calculate(Min(Table[Operational_TS]), filter(Table, Table[subevent] = "DISCHARGE-TRF")) ,calculate(Max(Table[Operational_TS]), filter(Table, Table[subevent] = "DISCHARGE-TRF")),day)),[_1])

[avpk729]

Dear @amitchandak , thank you so much for your response.

I tried to convert your template into a working measure in my report. But once I input the full table names, it is saying that the "Parameter is not the correct type"

I'm not sure which parameter is failing the validation, the red squidly line is starting from the "_1" position onwards.

My formula:

Dwell = SUMX(Summarize('E2E tA_E2EServiceDelivery_CombAgg','E2E tA_E2EServiceDelivery_CombAgg'[GeoSite_Cd], 'E2E tA_E2EServiceDelivery_CombAgg'[Shipment_No]"_1", datediff(calculate(Min('E2E tA_E2EServiceDelivery_CombAgg'[Operational_TS]), filter('E2E tA_E2EServiceDelivery_CombAgg', 'E2E tA_E2EServiceDelivery_CombAgg'[Subevent_Cd] = "DISCHARGE-TRF")) ,calculate(Max('E2E tA_E2EServiceDelivery_CombAgg'[Operational_TS]), filter('E2E tA_E2EServiceDelivery_CombAgg', 'E2E tA_E2EServiceDelivery_CombAgg'[Subevent_Cd] = "DISCHARGE-TRF")),day)),[_1])

Kinldy have a look and suggest where I might have done a typo.

I have tried both the 'AverageX' as well as "SumX' methods, same issue for both.

Regards

Pk