Forum Discussion

ponnusamy's avatar
ponnusamy
Icon for Solution Supplier rankSolution Supplier
5 years ago
Solved

Counting Number of Days to plot pie chart

This seems to be simple but I am not getting the right result. I have the following data 

 

Planes RepairedRepaired Date
Avro Canada C102 Jetliner1/6/2021
Avro Canada C102 Jetliner1/7/2021
Avro Canada C102 Jetliner1/8/2021
Avro York1/4/2021
BAC 1-111/5/2021
Beechcraft 90 King Air2/1/2021
Beechcraft 90 King Air2/2/2021
Beechcraft 90 King Air2/3/2021
Beechcraft A100 King Air2/5/2021
Beechcraft A100 King Air2/6/2021
Beechcraft A100 King Air2/7/2021
Beechcraft A100 King Air2/8/2021
Beechcraft B100 King Air1/10/2021
Beechcraft B100 King Air1/11/2021
Beechcraft B100 King Air1/12/2021
Boeing 7471/2/2021
Boeing 7471/3/2021
Boeing 7471/4/2021
Boeing 7471/5/2021
  

 

Now I would like to get table like this one

 

Number of Days to Repair/ Count of Days
42
33
12
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi ponnusamy ,

     

    According to my understanding, you want to calculate the needed days of repair and the count of planes which need the same days, right?

     

    You could firstly add a column to the original table:

    Number of Days to Repair =
VAR _minDate =
    MINX (
        FILTER (
            'Table',
            'Table'[Planes Repaired] = EARLIER ( 'Table'[Planes Repaired] )
        ),
        [Repaired Date]
    )
VAR _maxDate =
    MAXX (
        FILTER (
            'Table',
            'Table'[Planes Repaired] = EARLIER ( 'Table'[Planes Repaired] )
        ),
        [Repaired Date]
    )
RETURN
    DATEDIFF ( _minDate, _maxDate, DAY ) + 1

    And then use the following formula to create a new table:

    Table 2 =
ADDCOLUMNS (
    VALUES ( 'Table'[Number of Days to Repair] ),
    "Count of Days",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Planes Repaired] ),
            FILTER (
                'Table',
                'Table'[Number of Days to Repair]
                    = EARLIER ( 'Table'[Number of Days to Repair] )
            )
        )
)

    The final output is shown below:

    Please take a look at the pbix file here.

     

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

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    To do that, you need to make a disconnected table to hold your Number of Days to Repair Value with

     

    NumDays = GENERATESERIES(1,5,1)
     
    You can then make a table visual with the Value column from that table and add this measure (replace "Planes" with your actual table name).
     
    Count of Days =
    VAR vSummary =
        ADDCOLUMNS (
            DISTINCT ( Planes[Planes Repaired] ),
            "cDays",
                CALCULATE (
                    COUNT ( Planes[Repaired Date] )
                )
        )
    RETURN
        COUNTROWS (
            FILTER (
                vSummary,
                [cDays]
                    = SELECTEDVALUE ( NumDays[Value] )
            )
        )
     
     

     

    Pat
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi ponnusamy ,

     

    According to my understanding, you want to calculate the needed days of repair and the count of planes which need the same days, right?

     

    You could firstly add a column to the original table:

    Number of Days to Repair =
VAR _minDate =
    MINX (
        FILTER (
            'Table',
            'Table'[Planes Repaired] = EARLIER ( 'Table'[Planes Repaired] )
        ),
        [Repaired Date]
    )
VAR _maxDate =
    MAXX (
        FILTER (
            'Table',
            'Table'[Planes Repaired] = EARLIER ( 'Table'[Planes Repaired] )
        ),
        [Repaired Date]
    )
RETURN
    DATEDIFF ( _minDate, _maxDate, DAY ) + 1

    And then use the following formula to create a new table:

    Table 2 =
ADDCOLUMNS (
    VALUES ( 'Table'[Number of Days to Repair] ),
    "Count of Days",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Planes Repaired] ),
            FILTER (
                'Table',
                'Table'[Number of Days to Repair]
                    = EARLIER ( 'Table'[Number of Days to Repair] )
            )
        )
)

    The final output is shown below:

    Please take a look at the pbix file here.

     

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