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 ，

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

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

    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'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