Forum Discussion

Hauke's avatar
Hauke
Helper I
6 years ago
Solved

Derive missing data history chart from dates in table

Hey all, I would like to creat a visual showing the history of e.g. number of tickets with different status per month.

 

TicketAcceptedStart DateEnd Date
131.03.201903.04.201930.06.2019
231.03.201903.04.201915.07.2019
330.04.201924.05.201916.06.2019
430.04.201912.06.201920.07.2019

 

Should show something like ...

 

Does anyone has an ideas how I can solve that? And there are not just 4 but many many tickets ...

Would be very happy to get an ideas ... 

 

Best regards and thank you so much,

Hauke

Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi Hauke ,

    You need a separated Date Table, then you need to create the following two measures:

    Measure =
    CALCULATE (
        COUNT ( t2[Ticket] ),
        FILTER (
            t2,
            MONTH ( t2[Start Date] ) > MONTH ( MIN ( 'Table'[Date] ) )
                && MONTH ( t2[Accepted] ) <= MONTH ( MIN ( 'Table'[Date] ) )
        )
    )
     
    Measure 2 =
CALCULATE (
    COUNT ( t2[Ticket] ),
    FILTER (
        t2,
        MONTH ( t2[Start Date] ) <= MONTH ( MIN ( 'Table'[Date] ) )
            && MONTH ( t2[End Date] ) >= MONTH ( MIN ( 'Table'[Date] ) )
    )
)

    Results are as follows:

     

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU4uXCu3hK1PrFff8oEe_E8BvEyegfG9D6cQ9lZHLCWI4A?e=yzbYfS

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi Hauke ,

    I am not sure if the following measures are the result of your desired, adjust the conditions inside the measure, you may get the results you want:

    Before Start =
CALCULATE (
    COUNT ( t2[Ticket] ),
    FILTER (
        t2,
        (
            YEAR ( t2[Start Date] ) * 100
                + MONTH ( t2[Start Date] )
                > MIN ( 'Table'[Year Month Number] )
        )
            && (
                YEAR ( t2[Accepted] ) * 100
                    + MONTH ( t2[Accepted] )
                    <= MIN ( 'Table'[Year Month Number] )
                    && t2[End Date] <> BLANK ()
            )
    )
)
    In Progress =
    CALCULATE (
        COUNT ( t2[Ticket] ),
        FILTER (
            t2,
            YEAR ( t2[Start Date] ) * 100
                + MONTH ( t2[Start Date] )
                <= ( MIN ( 'Table'[Year Month Number] ) )
                && (
                    YEAR ( t2[End Date] ) * 100
                        + MONTH ( t2[End Date] )
                        >= ( MIN ( 'Table'[Year Month Number] ) )
                )
        )
    )

     

    No Start =
CALCULATE (
    COUNT ( t2[Ticket] ),
    FILTER (
        t2,
        (
            YEAR ( t2[Accepted] ) * 100
                + MONTH ( t2[Accepted] )
                <= MIN ( 'Table'[Year Month Number] )
        )
            && t2[Start Date] == BLANK ()
    )
)
    Not End =
CALCULATE (
    COUNT ( t2[Ticket] ),
    FILTER (
        t2,
        (
            YEAR ( t2[Start Date] ) * 100
                + MONTH ( t2[Start Date] )
                > MIN ( 'Table'[Year Month Number] )
        )
            && (
                YEAR ( t2[Accepted] ) * 100
                    + MONTH ( t2[Accepted] )
                    <= MIN ( 'Table'[Year Month Number] )
                    && t2[End Date] == BLANK ()
            )
    )
)

    Results are as follows:

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZR1JXlR9ilOiPtIpla3_moBfO33_zZtEl2HqQQZAzNfeQ?e=ts32b4

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    Hi Hauke ,

    You need a separated Date Table, then you need to create the following two measures:

    Measure =
    CALCULATE (
        COUNT ( t2[Ticket] ),
        FILTER (
            t2,
            MONTH ( t2[Start Date] ) > MONTH ( MIN ( 'Table'[Date] ) )
                && MONTH ( t2[Accepted] ) <= MONTH ( MIN ( 'Table'[Date] ) )
        )
    )
     
    Measure 2 =
CALCULATE (
    COUNT ( t2[Ticket] ),
    FILTER (
        t2,
        MONTH ( t2[Start Date] ) <= MONTH ( MIN ( 'Table'[Date] ) )
            && MONTH ( t2[End Date] ) >= MONTH ( MIN ( 'Table'[Date] ) )
    )
)

    Results are as follows:

     

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU4uXCu3hK1PrFff8oEe_E8BvEyegfG9D6cQ9lZHLCWI4A?e=yzbYfS

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Hauke's avatar
      Hauke
      Helper I
      6 years ago

      Hey Joey,

       

      this is perfect - exactly what I was looking for. Couldn't get it into my file so far... I will try and keep you updated. But definetly this is the solution! Thank you so much for your time and motivation!

       

      Best regards,

      Hauke

    • Hauke's avatar
      Hauke
      Helper I
      6 years ago

      Hey Joesh,

       

      I guess I found the issue ... if I add another year - it seems to crash it ...

       

       

      Leads to the following result :-(

       

       

      Cab you help me with this again?
      v-joesh-msft 

      • Hauke's avatar
        Hauke
        Helper I
        6 years ago

        Porbably just without MONTH ... 

        Measure 2 = CALCULATE(COUNT(t2[Ticket]); FILTER(t2; t2[Start Date]<=MIN('Table'[Date])&& t2[End Date]>=MIN('Table'[Date])))
         
        That seems to work ...?!
    • Hauke's avatar
      Hauke
      Helper I
      6 years ago

      Hey Joesh,

       

      so I missed to brief two topics - I learned now with your great solution ... the one thing I mentioned before - we also need to consider the year. And the second topic ... obvisiouly it is possible, that a ticket is not started or closed, yet ... so we also need to count the tickets in measure 1 not heaving a starting date and in measure 2 not having and end date :-( 

      Could you give me a hint?

      • v-joesh-msft's avatar
        v-joesh-msft
        Solution Sage
        6 years ago

        Hi Hauke ,

        I am not sure if the following measures are the result of your desired, adjust the conditions inside the measure, you may get the results you want:

        Before Start =
CALCULATE (
    COUNT ( t2[Ticket] ),
    FILTER (
        t2,
        (
            YEAR ( t2[Start Date] ) * 100
                + MONTH ( t2[Start Date] )
                > MIN ( 'Table'[Year Month Number] )
        )
            && (
                YEAR ( t2[Accepted] ) * 100
                    + MONTH ( t2[Accepted] )
                    <= MIN ( 'Table'[Year Month Number] )
                    && t2[End Date] <> BLANK ()
            )
    )
)
        In Progress =
        CALCULATE (
            COUNT ( t2[Ticket] ),
            FILTER (
                t2,
                YEAR ( t2[Start Date] ) * 100
                    + MONTH ( t2[Start Date] )
                    <= ( MIN ( 'Table'[Year Month Number] ) )
                    && (
                        YEAR ( t2[End Date] ) * 100
                            + MONTH ( t2[End Date] )
                            >= ( MIN ( 'Table'[Year Month Number] ) )
                    )
            )
        )

         

        No Start =
CALCULATE (
    COUNT ( t2[Ticket] ),
    FILTER (
        t2,
        (
            YEAR ( t2[Accepted] ) * 100
                + MONTH ( t2[Accepted] )
                <= MIN ( 'Table'[Year Month Number] )
        )
            && t2[Start Date] == BLANK ()
    )
)
        Not End =
CALCULATE (
    COUNT ( t2[Ticket] ),
    FILTER (
        t2,
        (
            YEAR ( t2[Start Date] ) * 100
                + MONTH ( t2[Start Date] )
                > MIN ( 'Table'[Year Month Number] )
        )
            && (
                YEAR ( t2[Accepted] ) * 100
                    + MONTH ( t2[Accepted] )
                    <= MIN ( 'Table'[Year Month Number] )
                    && t2[End Date] == BLANK ()
            )
    )
)

        Results are as follows:

        Here is a demo, please try it:

        https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZR1JXlR9ilOiPtIpla3_moBfO33_zZtEl2HqQQZAzNfeQ?e=ts32b4

        Best Regards,

        Community Support Team _ Joey
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.