Group by with multiple conditions Power Query Power BI

Question

I'm new to Power Bi.

I want to calculate Outstanding Jira Tickets and group the tickets yearly+Monthly. I did it using excel.

There are 7 columns(ticketID,CreatedDate,ResolvedDate,Createdyear,CreatedMonth,ResolvedYear and ResolvedMonth). What I want to do is calculate outstanding tickets for each year and month(EX: for 2021 Jan, How many outstanding tickets?).

The calculation mechanism is explained below.

To calculate Outstanding ticekts for eachyear(X) monthly(Y) wise,below logic is used.

A = All not resolved tickets(tickets with null value for resolvedDate)

B = Resolved null and CreatedYear = X and CreatedMonth >Y

C=ResolvedYear= X and ResolvedMonth>Yand CreatedYear<X

D= ResolvedYear = X and ResolvedMonth>Y and Createdyear= X and CreatedMonth<=Y

Outstanding tickets for Year(X) AND Month(Y)= A-B+C+D

Example:

Lets say if i want to calculate outstanding ticekts for 2022 May, then i need to find using below conditions.

A= All not resolved tickets(tickets with null value for resolvedDate)

B=Resolved null and CreatedYear 2022 and createdMonth>May

C=ResolvedYear 2022 and ResolvedMonth>May and CreatedYear<2022

D= ResolvedYear 2022 and ResolvedMonth>May and Createdyear 2022 and CreatedMonth<=May

Outstanding tickets for 2022 May= A-B+C+D

Please check the excel file using the attached link.

Corresponding excel query:

Used Excel Query=

=COUNTIF(ResolvedDate,"")-COUNTIFS(ResolvedDate,"",Created_Year,G2,Created_Month,CONCAT(">",MONTH(DATEVALUE(H2&" 1"))))+COUNTIFS(Resolved_Year,G2,Resolved_Month,CONCAT(">",MONTH(DATEVALUE(H2&" 1"))),Created_Year,CONCAT("<",G2))+COUNTIFS(Resolved_Year,G2,Resolved_Month,CONCAT(">",MONTH(DATEVALUE(H2&" 1"))),Created_Year,G2,Created_Month,CONCAT("<=",MONTH(DATEVALUE(H2&" 1"))))

What i tried with PowerBI:

So now i want to do this in PowerBI,

i get the data from excel and groupby using year and month using count rows, my idea is to change the groupby code in applied steps.

= Table.Group(#"Changed Type", {"CreatedYear", "CreatedMonth"}, {{"Count", each Table.RowCount(_), Int64.Type}})

but the problem is I don't know how to get summation and subtraction like i did it in powerBI.

Can someone please provide detailed instructions on creating columns and calculations or measures with formulas?

This is a duplicate of the same question you posted 23 hours ago at stackoverflow.com/questions/73128472/… — horseyride
– horseyride, Commented Jul 27, 2022 at 18:50

horseyride · Accepted Answer · 2022-07-27 20:56:57Z

Assuming Table1 looks like

You can use this code for Table1

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Created", type date}, {"Resolved", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #date(2021,1,1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Date.EndOfMonth([Custom])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Custom.1"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Create.Year", each Date.Year([Created])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Create.Month", each Date.Month([Created])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Resolve.Year", each Date.Year([Resolved])),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Resolve.Month", each Date.Month([Resolved]))
in #"Added Custom5"

and get

Assuming Table2 looks like

you can use this code for Table2

 let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "MonthNumber", each Date.Month(Date.FromText([Month]&"-01-2020"))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "a",(i)=>Table.RowCount(Table.SelectRows(Table1, each [Resolved]=null))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "b",(i)=>Table.RowCount(Table.SelectRows(Table1, each [Resolved]=null and i[Year]=[Create.Year] and [Create.Month]> i[MonthNumber]))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "c",(i)=>Table.RowCount(Table.SelectRows(Table1, each [Resolve.Year]=i[Year] and [Resolve.Month]>i[MonthNumber] and [Create.Year]< i[Year]))),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "d",(i)=>Table.RowCount(Table.SelectRows(Table1, each [Resolve.Year]=i[Year] and [Resolve.Month]>i[MonthNumber] and [Create.Year]= i[Year] and [Create.Month]<=i[MonthNumber]))),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Outstanding", each [a]-[b]+[c]+[d]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"MonthNumber", "a", "b", "c", "d"})
in    #"Removed Columns"

and get

Thanks a lot for this. I had no idea to fix this. Thanks again.

Collectives™ on Stack Overflow

Group by with multiple conditions Power Query Power BI

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related