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 Date].[Date],
(Table1[Direction] in {"Export"} ) && (LEFT(Table1[INCOTerms],1)="D") , Table1[Actual Delivery Date].[Date],
(Table1[Direction] in {"Export"}) && (LEFT(Table1[INCOTerms],1)<>"D") ,Table1[ATD].[Date],
BLANK()) 
To get the date between Actual Delivery Date and ATD if condition hits.
 
DateDiff = IF(DATEDIFF(Table1[Posted].[Date],Table1[Rules].[Date],DAY)>4,TRUE(),FALSE())
Posted minus the Rules date, if more than 4 day will return true.
 
I want to know which job is fall into billing more than 4 day.
 
 
 
WithinBilling = CALCULATE(COUNTROWS(DISTINCT(Table1)),FILTER(Table1,Table1[DateDiff]=FALSE() && NOT(ISBLANK(Table1[DateDiff]))))
 
NWithinBilling = CALCULATE(COUNTROWS(DISTINCT(Table1)),FILTER(Table1,Table1[DateDiff]=TRUE()))
I have a problem on each job have different ATD, Actual Delivery and Posted date.
 
*I would only want to get the earliest ATD, the latest Actual Delivery Date, and latest Actual Delivery Date of a job.
 
Expected outcome:
JobOperatorWithin 4 dayNot_W4Day
B246Alex 1
B007Rose1 
B456Jack1 
Each job only either in within 4 day or not in 4 day.

Please refer to my pbix: https://ufile.io/tvliygg7
 
Best thanks.
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

11 Replies

    • 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