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

NickProp28's avatar
NickProp28
Post Partisan
6 years ago

Dear Anonymous ,

 

Thanks for your time again.

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

Example,

JobOperator Within 4 dayNot W_4DAY
B678Peter1 
A123Rose 1

 

Best thanks.

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.

  • NickProp28's avatar
    NickProp28
    Post Partisan
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    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

  • NickProp28's avatar
    NickProp28
    Post Partisan
    6 years ago

    Dear Anonymous ,

     

    Thanks for your precious time.