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.

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.