Derive missing data history chart from dates in table

[Hauke]

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.06.2019
2	31.03.2019	03.04.2019	15.07.2019
3	30.04.2019	24.05.2019	16.06.2019
4	30.04.2019	12.06.2019	20.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

[v-joesh-msft · Accepted solution]

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/EU4uXCu3hK1PrFff8o...

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 · Accepted solution]

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/EU4uXCu3hK1PrFff8o...

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]

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 · Accepted solution]

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/EZR1JXlR9ilOiPtIpl...

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]

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]

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]

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