Date period trend column chart by year

[gabry90]

Hi all, i can't build my trend chart by date period

My table is like this:

the column "closed_duration" are only calculated column with this formula:

closed_duration = SUMX(FILTER(incident, NOT(ISBLANK(incident[closed_at]))), DATEDIFF(incident[opened_at], incident[closed_at], DAY))

beacuase I want to obtain the delta period between closed and opened time in days. And until this point is OK.

But now I want to regroup "closed_duration" in specific category labeled and show the grouped result as column chart.

I tried to make a categories with this formula (but i don't now if is correct):

group_by_closed_duration = SWITCH(
    TRUE(), 
    [closed_duration] <= 1, "1 day", 
    [closed_duration] > 1 && [closed_duration] <= 3, "2 days",
    [closed_duration] > 3 && [closed_duration] <= 7, "3-7 days",
    [closed_duration] > 7 && [closed_duration] <= 14, "1-2 week",
    [closed_duration] > 14 && [closed_duration] <= 31, "2-4 weeks",
    [closed_duration] > 31 && [closed_duration] <= 60, "1-2 months",
    [closed_duration] > 60, "+2 months")

The result expected that i want to obtain should be like this: (the column rapresent the ticket open grouped by "opened_at" and trend by "closed_at")

But i don't know how to do this report in Power Bi.

using service-now as ITIL tool for requests i can do this for example in this way:

but i can't reproduce a similar pattern inside Power Bi.

My actual fields are:

Anyone can help me? Thanks 🙂

[gabry90]

Thank you for you support. 

For security reasons I had to remove the other columns because they contain sensitive data that I can't share. For now there are 3 columns: identifier, opening date, closing date. These 3 columns should be enough to do what I would like.

sample.csv 

[v-jayw-msft]

Hi @gabry90 ,

Please share the sample data in text format so that we could test the formula.