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.   Ticket Accepted Start Date End Date 1 31.03.2019 03.04.2019 30.0...
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.

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.