Date Duplicate count

[NickProp28]

Dear Community,

I have set of data of this,

After export data to PBI, i created two column, 

Rules = SWITCH(TRUE(),

(Table1[Direction] in {"Import","Domestic"}) , Table1[Actual Delivery Date].[Date],

(Table1[Direction] in {"Export"} ) && (LEFT(Table1[INCOTerms],1)="D") , Table1[Actual Delivery Date].[Date],

(Table1[Direction] in {"Export"}) && (LEFT(Table1[INCOTerms],1)<>"D") ,Table1[ATD].[Date],

BLANK()) 

To get the date between Actual Delivery Date and ATD if condition hits.

DateDiff = IF(DATEDIFF(Table1[Posted].[Date],Table1[Rules].[Date],DAY)>4,TRUE(),FALSE())

Posted minus the Rules date, if more than 4 day will return true.

I want to know which job is fall into billing more than 4 day.

WithinBilling = CALCULATE(COUNTROWS(DISTINCT(Table1)),FILTER(Table1,Table1[DateDiff]=FALSE() && NOT(ISBLANK(Table1[DateDiff]))))

NWithinBilling = CALCULATE(COUNTROWS(DISTINCT(Table1)),FILTER(Table1,Table1[DateDiff]=TRUE()))

I have a problem on each job have different ATD, Actual Delivery and Posted date.

*I would only want to get the earliest ATD, the latest Actual Delivery Date, and latest Actual Delivery Date of a job.

Expected outcome:

Job	Operator	Within 4 day	Not_W4Day
B246	Alex		1
B007	Rose	1
B456	Jack	1

Each job only either in within 4 day or not in 4 day.

Please refer to my pbix: https://ufile.io/tvliygg7

Best thanks.

[lbendlin]

Great. What is your question?

[NickProp28]

Dear @lbendlin ,

Thanks for your attention on this matter.

Taking the job B789 as example, I would only want show '1' in within 4 day category instead of '3'.

'Within 4 day' formula will calculate how many false in my dataset row, but I just wanna count the row which have 'earliest date of ATD , latest date of Actual Delivery Date and latest date for Posted". 

For job A123, if I have this date formula, it wont duplicte count, will only show '1' either in Within 4 day or Not_W4day category.

Appreciate if you can help me on this.

[Anonymous]

Hi @NickProp28 ，

You can update the formula of measure "WithinBilling" as below:

WithinBilling = 
CALCULATE (
    COUNTROWS ( DISTINCT ( Table1 ) ),
    FILTER (
        Table1,
        Table1[DateDiff] = FALSE ()
            && NOT ( ISBLANK ( Table1[DateDiff] ) )
            && 'Table1'[ATD] = MIN ( 'Table1'[ATD] )
            && 'Table1'[Actual Delivery Date] = MAX ( 'Table1'[Actual Delivery Date] )
            && 'Table1'[Posted] = MAX ( 'Table1'[Posted] )
    )
)​

Best Regards

Rena

[NickProp28]

Dear @Anonymous ,

Thanks! 
I add the same code on NWithinBilling,

'Table1'[ATD] = MIN ( 'Table1'[ATD] )
            && 'Table1'[Actual Delivery Date] = MAX ( 'Table1'[Actual Delivery Date] )
            && 'Table1'[Posted] = MAX ( 'Table1'[Posted]

But Job B678 Peter has disappear. Understand that because Peter have complex date in ATD,Actual and Posted.

In this case, will priority on the row which have MIN ATD.

* I want one job only show in 'Withinbilling' or 'Nwithinbilling'.

Many thanks for yout time.

pbix = https://ufile.io/oz5l908b

[Anonymous]

Hi @NickProp28 ,

I just updated the formula of measure "WithinBilling" and "NWithinBilling" , you can check the details in the updated file.

WithinBilling = 
CALCULATE (
    DISTINCTCOUNT('Table1'[Operator ]),
    FILTER (
        Table1,
        Table1[DateDiff] = FALSE ()
            && NOT ( ISBLANK ( Table1[DateDiff] ) )
    )
)

NWithinBilling = CALCULATE(DISTINCTCOUNT('Table1'[Operator ]),FILTER(Table1,Table1[DateDiff]=TRUE() ))

Best Regards

Rena

[NickProp28]

Dear @Anonymous ,

Thanks for your time again.

But my expected outcome is one job only in 'Within 4day' or 'Not_Within4day'. 

Example,

Job	Operator	Within 4 day	Not W_4DAY
B678	Peter	1
A123	Rose		1

Best thanks.

[Anonymous]

Hi @NickProp28 ,

When both of Within 4 day and Not W_4DAY have values, only display one of them? If yes, which criteria need to follow when display the data? Could you please provide the calculation logic of Job B678 and A123? Why job B678 Peter display 1 in field Within 4 day and job A123 Rose display 1 only in field Not W_4DAY? Thank you.

Best Regards

Rena

[NickProp28]

Dear @Anonymous ,

I hope you doing well.

Sorry to ask, is it I have to create a new table with RELATED(), so can remove all the duplicate job and perform my following steps. Thus,I can get only '1' in WithibBilling or NWithinBilling.

I try RELATED function, but does not work well for me. 

Can you kindly advice me on this. Appreciate any help and thanks for you time.

[Anonymous · Accepted solution]

Hi @NickProp28 ,

I create another 2 new measures for your requirement, please check whether they are what you want. You can find the details in this updated file.

New_WithinBilling = 
VAR _tab =
    SUMMARIZE (
        'Table1',
        'Table1'[Job],
        'Table1'[Operator ],
        'Table1'[Direction],
        'Table1'[INCOTerms],
        "NATD", MIN ( 'Table1'[ATD] ),
        "NActual Delivery Date", MAX ( 'Table1'[Actual Delivery Date] ),
        "NPosted", MAX ( 'Table1'[Posted] ),
        "NRules", SWITCH (
            TRUE (),
             ( MAX ( 'Table1'[Direction] ) IN { "Import", "Domestic" } ), MAX ( 'Table1'[Actual Delivery Date] ),
             ( MAX ( 'Table1'[Direction] ) IN { "Export" } )
                && ( LEFT ( MAX ( 'Table1'[INCOTerms] ), 1 ) = "D" ), MAX ( 'Table1'[Actual Delivery Date] ),
             ( MAX ( 'Table1'[Direction] ) IN { "Export" } )
                && ( LEFT ( MAX ( 'Table1'[INCOTerms] ), 1 ) <> "D" ), MIN ( 'Table1'[ATD] ),
            BLANK ()
        )
    )
RETURN
    IF (
        DATEDIFF ( MAXX ( _tab, [NPosted] ), MAXX ( _tab, [NRules] ), DAY ) < 4,
        1,
        BLANK()
    )

New_NWithinBilling = if([New_WithinBilling],BLANK(),1)

Best Regards

Rena

[NickProp28]

Dear @Anonymous ,

Thanks for your precious time.

[NickProp28]

Dear @Anonymous ,

Sorry for I mislead you from the start.

From dataset, (lets excldude Rules and DateDiff)
Taking example Job 123, have 2 row in dataset. I would want to determine the MIN ATD, MAX Actual and MAX Posted.

Get one row now, ATD (15 march 2020), Actual (27 March 2020) and Posted (27 March 2020). >>1 step

Then perform Rules and DateDiff. (False/True) >>Step 2 and 3

Perform WithinBilling and NWithinBilling formula.  >>Step 4
So now table only have '1' in either WithinBilling or NWithinBilling. 
Expected Outcome:

Operator	Within4day	Not_W4day
Rose	1
Mo	1
Total	2

For Peter B678 job,  retrieve ATD (20 February), Actual (18 March), Posted (20 February).

Sorry again and appreciate your help.