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

lbendlin's avatar
lbendlin
Super User
6 years ago

Great. What is your question?

NickProp28's avatar
NickProp28
Post Partisan
6 years ago

Dear lbendlin ,

 

Thanks for your attention on this matter.

Taking the job B789 as example, I would only want show '1' in within 4 day category instead of '3'.

'Within 4 day' formula will calculate how many false in my dataset row, but I just wanna count the row which have 'earliest date of ATD , latest date of Actual Delivery Date and latest date for Posted". 

For job A123, if I have this date formula, it wont duplicte count, will only show '1' either in Within 4 day or Not_W4day category.

Appreciate if you can help me on this.

 

 

 

  • 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