Forum Discussion

Frank225's avatar
Frank225
Regular Visitor
6 years ago

Distinct count of multiple columns created from the same record

Dear community,   For logistic matters I have 3 columns in my dataset: - TruckID - StartDate - EndDate   Goal: Know on how many unique days each truck was active   I merged the columns Truck...
Need Help
V-lianl-msft's avatar
V-lianl-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Frank225 ,

 

You can first create a calculated column to get the date difference.

DIFF = DATEDIFF('Table'[StartDate],'Table'[EndDate]+1,DAY)

Then create a measure to calculate the total number of working days for each truck.

TOTAL = CALCULATE(SUM('Table'[DIFF]),ALLEXCEPT('Table','Table'[TruckID]))

Sample .pbix

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Frank225's avatar
Frank225
Regular Visitor
6 years ago

V-lianl-msft 

 

Thank you for your clear explanation so far. I would like to go 1 step further:

 

Assume the second record is:

TruckID = A

StartDate = Thursday, January 9, 2020

EndDate = Thursday, January 9, 2020

 

In your sample the total for truck A remains 6. But in fact I would like to see 5 there. 

 

TruckID=A

Record 1: Jan 3rd - Jan 5th = 3 days

Record 2: Jan 9th - Jan 9th = 1 day

Record 3: Jan 9th - Jan 10th = 2 days

This sums up to 6 days, but doing a distinct count on the active days should give me a 3-0-2 or a 3-1-1 (5 days) because of the duplicate value 9th Jan in record 2 and 3. The truck only started on Jan 3, 4, 5, 9 and 10. 

 

Can you help me solving this issue?

 

 

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Frank225 ,

     

    Try to create a calculated column like this:

    Column 2 =
VAR max_date =
    CALCULATE (
        MAX ( 'Table (2)'[EndDate] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[StartDate], 'Table (2)'[TruckID] )
    )
RETURN
    IF (
        'Table (2)'[EndDate] <> max_date,
        0,
        'Table (2)'[EndDate].[Day] - 'Table (2)'[StartDate].[Day] + 1
    )

    Sample .pbix has been updated.

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Frank225's avatar
      Frank225
      Regular Visitor
      6 years ago
       

      V-lianl-msft

      I have tested it on my dataset and I believe it is very close to the solution now. With increasing records for truck A you see all jobs done on the same day ('Column' = 1) are correct now. If 'Column' > 1 it still seems that no correction for overlapping dates is in place. For example:

      3-1-2020 till 5-1-2020 = 3

      5-1-2020 till 9-1-2020 = 5

      9-1-2020 till 10-1-2020 = 2 

      This should be in total 8 days instead of 10. --> 3-1-2020 till 10-1-2020 is 8 days. 

       

       

      Thanks in advance!

       
      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Frank225 ,

         

        Could you share the sample pbix via cloud service like onedrive for business?

        Please mask any sensitive data before uploading.

         

        Best Regards,
        Liang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.