cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Hauke Frequent Visitor
Frequent Visitor

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

pbi need help.PNG

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-joesh-msft Established Member
Established Member

Re: Derive missing data history chart from dates in table

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:

31.PNG

 

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.

 

View solution in original post

v-joesh-msft Established Member
Established Member

Re: Derive missing data history chart from dates in table

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:

32.PNG

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.

View solution in original post

6 REPLIES 6
v-joesh-msft Established Member
Established Member

Re: Derive missing data history chart from dates in table

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:

31.PNG

 

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.

 

View solution in original post

Hauke Frequent Visitor
Frequent Visitor

Re: Derive missing data history chart from dates in table

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 Frequent Visitor
Frequent Visitor

Re: Derive missing data history chart from dates in table

Hey Joesh,

 

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

 

1.PNG

 

Leads to the following result 😞

 

2.PNG

 

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

Hauke Frequent Visitor
Frequent Visitor

Re: Derive missing data history chart from dates in table

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 Frequent Visitor
Frequent Visitor

Re: Derive missing data history chart from dates in table

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 Established Member
Established Member

Re: Derive missing data history chart from dates in table

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:

32.PNG

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.

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 451 members 4,522 guests
Please welcome our newest community members: