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 ,

 

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.