Home » Count Out-In Quarter Difference

Count Out-In Quarter Difference

(Excel formulas also welcome) List the count of quarters for only those rows where Out Quarter > In Quarter. Out Quarter will not be counted in qualifying rows. Hence for Y2022Q1 – Y2022Q4, Out Quarter > In Quarter, hence Y2022Q1, Y2022Q2 and Y2022Q3 will be counted. But, Y2022Q4 will not be counted. Y2022Q2-Y2022Q2 will not be considered as Out Quarter not > In Quarter

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 18
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Count Out-In Quarter Difference with Power Query

Power Query solution 1 for Count Out-In Quarter Difference, proposed by Bhavya Gupta: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Fn = (dt) =>

let
 b = Number.FromText(Text.AfterDelimiter(dt,"Q")),
 a = hashtag#date(Number.FromText(Text.BetweenDelimiters(Text.From(dt, "en-IN"), "Y", "Q")),
 (b-1)*2+b,1)
in
 a,
 AllQtrs = Table.AddColumn(Source, "Quarter", each List.Generate(()=> [x=Fn([In Quarter]),
 y=Fn([Out Quarter])], each [x]< [y], each [x=Date.AddQuarters([x],1), y=[y]],
 each "Y"& Number.ToText( Date.Year([x]))&"Q"&Number.ToText( Date.QuarterOfYear([x])))),
 RemovedNonCriterias = Table.SelectRows(Table.ExpandListColumn(AllQtrs, "Quarter"), each ([Quarter] <> null)),
 RemovedOtherColumns = Table.SelectColumns(RemovedNonCriterias,{"Quarter"}),
 Grouped = Table.Group(RemovedOtherColumns, {"Quarter"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
 Sorted = Table.Sort(Grouped,{{"Quarter", Order.Ascending}})
in
 Sorted
Power Query solution 2 for Count Out-In Quarter Difference, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData18"]}[Content], 
  ChangeType = Table.TransformColumnTypes(
    Source, 
    {{"In Quarter", type text}, {"Out Quarter", type text}}
  ), 
  Transform = Table.AddColumn(
    ChangeType, 
    "Quarter", 
    each 
      let
        _Out = [
          y = Number.From(Text.Middle([Out Quarter], 1, 4)), 
          q = Number.From(Text.Middle([Out Quarter], 6, 1))
        ], 
        _Result = List.Generate(
          () => [
            y = Number.From(Text.Middle([In Quarter], 1, 4)), 
            q = Number.From(Text.Middle([In Quarter], 6, 1))
          ], 
          each (_[y] < _Out[y] or (_[y] = _Out[y] and _[q] < _Out[q])), 
          each if (_[q] = 4) then [y = [y] + 1, q = 1] else [y = [y], q = [q] + 1], 
          each "Y" & Text.From(_[y]) & "Q" & Text.From(_[q])
        )
      in
        _Result
  ), 
  Filter = Table.SelectRows(Transform, each (List.Count([Quarter]) > 0)), 
  Expand = Table.ExpandListColumn(Filter[[Quarter]], "Quarter"), 
  Group = Table.Group(Expand, {"Quarter"}, {{"Count", each Table.RowCount(_), Int64.Type}}), 
  Sort = Table.Sort(Group, {{"Quarter", Order.Ascending}})
in
  Sort
Power Query solution 3 for Count Out-In Quarter Difference, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
Part2:
#"Expanded Quarters" = Table.ExpandListColumn(#"Added Custom2", "Quarters"),
 #"Filtered Rows" = Table.SelectRows(#"Expanded Quarters", each ([Quarters] <> null)),
 #"Added Custom4" = Table.AddColumn(#"Filtered Rows", "Date", each Date.AddQuarters([Date In],[Quarters])),
 #"Sorted Rows" = Table.Sort(#"Added Custom4",{{"Date", Order.Ascending}}),
 #"Added Custom3" = Table.AddColumn(#"Sorted Rows", "Quarters.", each "Y"&Text.From(Date.Year(Date.AddQuarters([Date In],[Quarters])))&"Q"&Text.From(Date.QuarterOfYear(Date.AddQuarters([Date In],[Quarters])))),
 #"Grouped Rows" = Table.Group(#"Added Custom3", {"Quarters."}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
 #"Grouped Rows"
Power Query solution 4 for Count Out-In Quarter Difference, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Quarter", type text}, {"Out Quarter", type text}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each hashtag#date(Number.From(Text.Middle([In Quarter],1,4)),if Number.From(Text.End([In Quarter],1))=1 then 3 else if Number.From(Text.End([In Quarter],1))=2 then 6 else if Number.From(Text.End([In Quarter],1))=3 then 9 else 12,15)),
 #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each hashtag#date(Number.From(Text.Middle([Out Quarter],1,4)),if Number.From(Text.End([Out Quarter],1))=1 then 3 else if Number.From(Text.End([Out Quarter],1))=2 then 6 else if Number.From(Text.End([Out Quarter],1))=3 then 9 else 12,15)),
 #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Date In"}, {"Custom.1", "Date Out"}}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date In", type date}, {"Date Out", type date}}),
 #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Quarters", each {0..Number.RoundDown(Duration.Days([Date Out]-[Date In])/90,0)-1}),

Solving the challenge of Count Out-In Quarter Difference with Excel

Excel solution 1 for Count Out-In Quarter Difference, proposed by John V.: 
=LET(r,B3:C9,q,DATE(MID(r,2,4),3*RIGHT(r),1),m,MIN(q),
d,EDATE(m,3*(SEQUENCE(DATEDIF(m,MAX(q),"m")/3)-1)),
HSTACK("Y"&YEAR(d)&"Q"&MONTH(d)/3,MAP(d,LAMBDA(x,SUM((x>=TAKE(q,,1))*(x
Excel solution 2 for Count Out-In Quarter Difference, proposed by 🇰🇷 Taeyong Shin: 
=LET(d,B3:C9,yq,TOCOL(SORT(UNIQUE(TOCOL(REGEXREPLACE(d,"d$",))))&{1,2,3,4}),in,TAKE(d,,1),n,MMULT((yq>=TOROW(in))*(yq
Excel solution 3 for Count Out-In Quarter Difference, proposed by Aditya Kumar Darak 🇮🇳:
Excel solution 4 for Count Out-In Quarter Difference, proposed by Oscar Javier Rosero Jiménez: 
=INDEX(_OmayI,b,1).
=LET(datos,B3:C9,
InOut,--SUBSTITUTE(SUBSTITUTE(datos,"Y",""),"Q",""),
InOutF,DATE(LEFT(InOut,4),RIGHT(InOut,1)*3,1),
_min,MIN(InOutF),
_max,MAX(InOutF),
_SEQ,UNIQUE(SEQUENCE(_max-_min,,_min)),
_uf,UNIQUE("Y"&YEAR(_SEQ)&"Q"&ROUNDUP(MONTH(_SEQ)*30/90,0)),
_AdityaKumarDarak, "Final Similar",
_OmayI,(FILTER(datos,INDEX(datos,,1)=INDEX(_OmayI,b,1))*((_uf
Excel solution 5 for Count Out-In Quarter Difference, proposed by Sarun Chimamphant: 
=LET(a,B3:C9,b,SORT(MID(a,2,4)*10+RIGHT(a)),d,SORT(UNIQUE(TOCOL(MID(a,2,4)*10+RIGHT(a)))),mi,MIN(d),ma,MAX(d),nxt,LAMBDA(x,LET(y,LEFT(x,4),q,--RIGHT(x),IF(q<4,y*10+q+1,(y+1)*10+1))),e,REDUCE(mi,SEQUENCE(20),LAMBDA(acc,cur,LET(n,nxt(TAKE(acc,-1)),IFERROR(VSTACK(acc,IF(n""),g,BYROW(f,LAMBDA(x,SUM(IF((x>=INDEX(b,,1))*(x
Excel solution 6 for Count Out-In Quarter Difference, proposed by Agah Dikici: 
=LET(in,DATE(MID(B3:B9,2,4),RIGHT(B3:B9)*3,1),
out,DATE(MID(C3:C9,2,4),RIGHT(C3:C9)*3,1),
l,SEQUENCE(MAX(out)-MIN(in),,MIN(in)),
u,SORT(FILTER(l,(MOD(MONTH(l),3)=0)*DAY(l)=1)),
HSTACK("Y"&YEAR(u)&"Q"&MONTH(u)/3,BYROW(u,LAMBDA(x,SUM(--(x>=in)*(x

Leave a Reply