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

      • 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.