Power Query Count By Quarter

[OrkA]

Hi,

Assume I have the next table:                                         

Tool ID	Build Qtr	Ship Qtr
1	CY22Q1	CY22Q3
2	CY20Q3	CY21Q1
3	CY22Q1	CY22Q2
4	CY22Q2	CY22Q2

for each row i want to check if Build Qtr<Ship Qtr and if so count all the Quarters(X) that fulfill the condition:

Build QTR<= X <Ship Qtr.

for example in the table above:

for row 1-> CY22Q1<CY22Q3 Therefore --->  CY22Q1 0->1  CY22Q2 0->1

for row 2-> CY20Q3<CY21Q1 Therefore----> CY20Q3  0->1  CY20Q4 0->1

for row 3 ->CY22Q1<CY22Q2 Therefore----> CY22Q1  1->2  

for row 4 ->CY22Q2=CY22Q2 (dont do nothing).

Can i do it in power bi?

Thanks

Edit:

Result for this example should be:

CY20Q3	1
CY20Q4	1
CY22Q1	2
CY22Q2	1

[Vijay_A_Verma]

Can you please make a result table and post the result?

For row 2 - CY20Q3 and CY20Q4 both should come

[OrkA]

Hi,

Yes you're right sorry.

Added results to original post.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKONDIKhDOMlWJ1opWMIFwDIBfMMAQqAIkbY6g3AoubILgI8VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ToolID = _t, BuildQtr = _t, ShipQtr = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ToolID", Int64.Type}, {"BuildQtr", type text}, {"ShipQtr", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [BuildQtr]<[ShipQtr] then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"BuildQtr"}, {{"Total", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

[OrkA]

Thanks but its more complicated.

I want to count all the quarters between Build Qtr and Ship Qtr.

take a look at the example again please.

[Vijay_A_Verma · Accepted solution]

Use this. I have assumed that century is 2000, hence 22 will be 2022...This I have done for the sake of simplicity otherwise I can fit in the logic what should be treated as 1900 and what 2000 but I have avoided this and made a simple assumption.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKONDIKhDOMlWJ1opWMIFwDIBfMMAQqAIkbY6g3AoubILgI8VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ToolID = _t, BuildQtr = _t, ShipQtr = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ToolID", Int64.Type}, {"BuildQtr", type text}, {"ShipQtr", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "BuildQtrDate", each Date.FromText("20"&Text.Middle([BuildQtr],2,2)&"-"&Text.From(Number.From(Text.End([BuildQtr],1))*3)&"-1")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ShipQtrDate", each Date.FromText("20"&Text.Middle([ShipQtr],2,2)&"-"&Text.From(Number.From(Text.End([ShipQtr],1))*3)&"-1")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ListOfQuartersDate", each List.Generate(()=>[x=[BuildQtrDate],y=[ShipQtrDate]], each [x]<[y], each [x=Date.AddQuarters([x],1),y=[y]], each [x])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each List.Transform([ListOfQuartersDate],(i)=>"CY"&Text.End(Text.From(Date.Year(i)),2)&"Q"&Text.From(Date.Month(i)/3))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Custom", Order.Ascending}})
in
    #"Sorted Rows"