Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hauke
Helper I
Helper I

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
v-joesh-msft
Solution Sage
Solution Sage

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

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
Solution Sage
Solution Sage

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.

 

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?

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.

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 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.