Forum Discussion

NickProp28's avatar
NickProp28
Post Partisan
6 years ago
Solved

Date Duplicate count

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
NickProp28
Post Partisan
6 years ago

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:

OperatorWithin4dayNot_W4day
Rose1 
Mo1 
Total2 

 

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

Sorry again and appreciate your help.