Conditional summation based on Product Type

[rphang]

Hi everyone, 

I have a situation which I need help on for my PBI Dashboard. I would like to dynamically calculate monthly both "Novelty" and "Normal" Total Qty and Order Value based on this condition,

- The first 3 months of launch, Product Type is considered "Novelty"

- After 3 mths, Product Type is considered as "Normal"

- Any product launched mid month (e.g 15th), will be considered launch first day of the mth

Example Nov 2022, the coding should sum the figures before Sep 2022 launched under "Normal" and the remaining figures under "Novelty".

The end result is to have the Total Qty and Order Value display in Bar, Line Chart or even a Matrix Table within the Dashboard.

	Qty	Qty	Qty	Qty	Order Value	Order Value	Order Value	Order Value
	Nov-22	Dec-22	Jan-23	Feb-23	Nov-22	Dec-22	Jan-23	Feb-23
Novelty	46,955	10,875	24,475	-	262,182	81,034	23,225	-
Normal	6,940	36,335	44,240	99,270	33,887	245,779	313,162	504,384

[v-jianboli-msft · Accepted solution]

Hi @rphang ,

Please try:

First, unpivot your data like this:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVdNb9swDP0rRs6KIVLfx2LFDtvQDSh6KnbIsmDbYe2wpfv9k23VkUhFtqdLLImxH6lHPurxcXf//mEndp9+P399OZ67u8PPU5x+OLw8Hb93t4fzMHt4+nHu3jz/Ocfnu+e/e8T4cHs6Tg/vDk97VPHh7enL9LBs81k87m6khOzLw7OE/fxf3RsZf7p5OGUucxAA17YhCJTl5jCUUNKzVS1CMGtsvRU6vjUhxww5TsjvT78m5La32ddRKF2CQTSrt0d/jNBaM4wgtN9imZCrDLkiyMH0JHCOznVjnx9KB1IYehYQ7dhazc4Ka+aI6wy3JlxBCjvoIsQeN21PvvjAV42wepttQm8y9IZEHXss+GJLfli5enf+snOc03G14mfLNmG3GXZLsJsiS5XAPDy2RNfeHQPp+FkoG3NxvV3C7DLMjuUnOS0z+1ApHc3NyS1XVhBQGBNug2HC7DPMnmZmb5qUVSVJmttpaKEcsNVhw3JCNI0T/pDhDyzmumTxUl1ZLIq2mopaOGVXrsayOCfoWLdmKZIs+qFJXFLRl1IUUJgK9gEoVhXKV1cv4AsdBQLeXcoLZTAp2EsJCsqXFmm4qKFYrmJUUOqLdsLMVQVyBQUkBd31LQJznTFGXj2AeRXsKuhjIAK1BS/CXF4gF1FQBLzs7QYyr0vWIXwVZR+AKe4rCFvxavRidiHX03ECZv/xeJ5cUD20889tS+c0bOBERoGBO+aFrJzWZJscyCV1nOQOyN41CQNmfUJnbjlXjaqsFUxbUdvxy/7Vg1xYwTZZtKXfrbRhyU/Li+AAVZPKHhs0Uwc/vCGBzxUWHAGvet+k/FLPe4U/3vBA+9h8VbleM47JFmtN8iDX23GSEwgDa4Vbre+KLMaoN5TTKHlz40G4SvfghbvUn1xqx0mRvKwdBnKVKvrfims1+mvhKVIluRzEwm8r6N1I0Qk95lqLSWtvXr4l9JYFvnHxWxZbtDzsKvACHzS/gQQ1vi/BzlUWgcCWPbjdNYZv1NkLTF4uh7f5UFs2hjdo8cP4yncs7qxI8ENPihi9+f1Hh9mRs8usoZabwVZbzME4uZDrLirKHNrXL9F8De0HOvOqGf1fvRqHd681H3PZRc0PoVnHN/aZ1Xc0fL3WOGU5kIsuGga/kamtpu3KzSoWzMCpXl/thuNUJPWNCEPD+fkf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#".0" = _t, #" .1" = _t, #" .2" = _t, #" .3" = _t, Qty = _t, Qty.1 = _t, Qty.2 = _t, Qty.3 = _t, #"Order Amt" = _t, #"Order Amt.1" = _t, #"Order Amt.2" = _t, #"Order Amt.3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{".0", type text}, {" .1", type text}, {" .2", type text}, {" .3", type text}, {"Qty", type text}, {"Qty.1", type text}, {"Qty.2", type text}, {"Qty.3", type text}, {"Order Amt", type text}, {"Order Amt.1", type text}, {"Order Amt.2", type text}, {"Order Amt.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Qty", "Qty.1", "Qty.2", "Qty.3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"SKU", type text}, {"Product Name", type text}, {"Launch Date", type date}, {"Unit Cost", type number}, {"Nov-22", Int64.Type}, {"Dec-22", Int64.Type}, {"Jan-23", Int64.Type}, {"Feb-23", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"SKU", "Product Name", "Launch Date", "Unit Cost"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "MonthYear"}, {"Value", "Order Amt"}})
in
    #"Renamed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVdNb9swDP0rRs6KIVLfx2LFDtvQDSh6KnbIsmDbYe2wpfv9k23VkUhFtqdLLImxH6lHPurxcXf//mEndp9+P399OZ67u8PPU5x+OLw8Hb93t4fzMHt4+nHu3jz/Ocfnu+e/e8T4cHs6Tg/vDk97VPHh7enL9LBs81k87m6khOzLw7OE/fxf3RsZf7p5OGUucxAA17YhCJTl5jCUUNKzVS1CMGtsvRU6vjUhxww5TsjvT78m5La32ddRKF2CQTSrt0d/jNBaM4wgtN9imZCrDLkiyMH0JHCOznVjnx9KB1IYehYQ7dhazc4Ka+aI6wy3JlxBCjvoIsQeN21PvvjAV42wepttQm8y9IZEHXss+GJLfli5enf+snOc03G14mfLNmG3GXZLsJsiS5XAPDy2RNfeHQPp+FkoG3NxvV3C7DLMjuUnOS0z+1ApHc3NyS1XVhBQGBNug2HC7DPMnmZmb5qUVSVJmttpaKEcsNVhw3JCNI0T/pDhDyzmumTxUl1ZLIq2mopaOGVXrsayOCfoWLdmKZIs+qFJXFLRl1IUUJgK9gEoVhXKV1cv4AsdBQLeXcoLZTAp2EsJCsqXFmm4qKFYrmJUUOqLdsLMVQVyBQUkBd31LQJznTFGXj2AeRXsKuhjIAK1BS/CXF4gF1FQBLzs7QYyr0vWIXwVZR+AKe4rCFvxavRidiHX03ECZv/xeJ5cUD20889tS+c0bOBERoGBO+aFrJzWZJscyCV1nOQOyN41CQNmfUJnbjlXjaqsFUxbUdvxy/7Vg1xYwTZZtKXfrbRhyU/Li+AAVZPKHhs0Uwc/vCGBzxUWHAGvet+k/FLPe4U/3vBA+9h8VbleM47JFmtN8iDX23GSEwgDa4Vbre+KLMaoN5TTKHlz40G4SvfghbvUn1xqx0mRvKwdBnKVKvrfims1+mvhKVIluRzEwm8r6N1I0Qk95lqLSWtvXr4l9JYFvnHxWxZbtDzsKvACHzS/gQQ1vi/BzlUWgcCWPbjdNYZv1NkLTF4uh7f5UFs2hjdo8cP4yncs7qxI8ENPihi9+f1Hh9mRs8usoZabwVZbzME4uZDrLirKHNrXL9F8De0HOvOqGf1fvRqHd681H3PZRc0PoVnHN/aZ1Xc0fL3WOGU5kIsuGga/kamtpu3KzSoWzMCpXl/thuNUJPWNCEPD+fkf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#".0" = _t, #" .1" = _t, #" .2" = _t, #" .3" = _t, Qty = _t, Qty.1 = _t, Qty.2 = _t, Qty.3 = _t, #"Order Amt" = _t, #"Order Amt.1" = _t, #"Order Amt.2" = _t, #"Order Amt.3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{".0", type text}, {" .1", type text}, {" .2", type text}, {" .3", type text}, {"Qty", type text}, {"Qty.1", type text}, {"Qty.2", type text}, {"Qty.3", type text}, {"Order Amt", type text}, {"Order Amt.1", type text}, {"Order Amt.2", type text}, {"Order Amt.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Order Amt", "Order Amt.1", "Order Amt.2", "Order Amt.3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"SKU", type text}, {"Product Name", type text}, {"Launch Date", type date}, {"Unit Cost", type number}, {"Nov-22", Int64.Type}, {"Dec-22", Int64.Type}, {"Jan-23", Int64.Type}, {"Feb-23", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"SKU", "Product Name", "Launch Date", "Unit Cost"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "MonthYear"}, {"Value", "Qty"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"MonthYear", "Unit Cost", "Launch Date", "Product Name", "SKU"}, #"Order Amt", {"MonthYear", "Unit Cost", "Launch Date", "Product Name", "SKU"}, "Order Amt", JoinKind.Inner),
    #"Expanded Order Amt" = Table.ExpandTableColumn(#"Merged Queries", "Order Amt", {"SKU", "Product Name", "Launch Date", "Unit Cost", "MonthYear", "Order Amt"}, {"Order Amt.SKU", "Order Amt.Product Name", "Order Amt.Launch Date", "Order Amt.Unit Cost", "Order Amt.MonthYear", "Order Amt.Order Amt"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Order Amt",{"Order Amt.SKU", "Order Amt.Product Name", "Order Amt.Launch Date", "Order Amt.Unit Cost", "Order Amt.MonthYear"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Order Amt.Order Amt", "Order Amt"}})
in
    #"Renamed Columns1"

Then create a Date table:

Manage relationship between the tables:

Apply the measures:

Normal(order) = CALCULATE(SUM('Table'[Order Amt]),FILTER('Table',[Launch Date]<=EOMONTH(MAX('Date'[Date]),-3)))

Normal(Qty) = CALCULATE(SUM('Table'[Qty]),FILTER('Table',[Launch Date]<=EOMONTH(MAX('Date'[Date]),-3)))

Novelty(Order) = CALCULATE(SUM('Table'[Order Amt]),FILTER('Table',[Launch Date]>EOMONTH(MAX('Date'[Date]),-3)&&[Launch Date]<=MAX('Date'[Date])))

Novelty(Qty) = CALCULATE(SUM('Table'[Qty]),FILTER('Table',[Launch Date]>EOMONTH(MAX('Date'[Date]),-3)&&[Launch Date]<=MAX('Date'[Date])))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rphang]

Sorry forgot to include my raw data,

				Qty	Qty	Qty	Qty	Order Amt	Order Amt	Order Amt	Order Amt
SKU	Product Name	Launch Date	Unit Cost	Nov-22	Dec-22	Jan-23	Feb-23	Nov-22	Dec-22	Jan-23	Feb-23
A001	Product 1	01-Nov-22	4.50	735	1,110	735	19,200	3,308	4,995	3,308	86,400
A002	Product 2	01-Sep-22	6.60	2,340	225	2,340	225	15,444	1,485	15,444	1,485
A003	Product 3	01-Sep-22	15.00	700	740	700	1,110	10,500	11,100	10,500	16,650
A004	Product 4	01-Nov-22	2.00	945	2,820	945	2,820	1,890	5,640	1,890	5,640
A005	Product 5	01-Sep-22	2.20	2,625	2,600	2,625	2,600	5,775	5,720	5,775	5,720
A006	Product 6	01-Sep-22	5.50	3,240	6,600	3,240	6,600	17,820	36,300	17,820	36,300
A007	Product 7	01-Sep-22	6.90	550	19,200	550	19,200	3,795	132,480	3,795	132,480
A008	Product 8	01-Sep-22	1.55	2,820	300	2,820	300	4,371	465	4,371	465
A009	Product 9	01-Sep-22	6.40	2,600	740	740	225	16,640	4,736	4,736	1,440
A010	Product 10	01-Sep-22	1.90	6,600	225	2,625	2,600	12,540	428	4,988	4,940
A011	Product 11	01-Sep-22	7.20	19,200	1,110	3,240	6,600	138,240	7,992	23,328	47,520
A012	Product 12	01-Nov-22	7.20	300	1,110	550	2,625	2,160	7,992	3,960	18,900
A013	Product 13	01-Nov-22	0.60	740	225	2,820	300	444	135	1,692	180
A014	Product 14	15-Oct-22	3.10	225	740	2,600	740	698	2,294	8,060	2,294
A015	Product 15	15-Oct-22	0.70	1,110	150	6,600	225	777	105	4,620	158
A016	Product 16	01-Nov-22	0.60	1,110	735	19,200	1,110	666	441	11,520	666
A017	Product 17	01-Nov-22	3.80	225	2,340	225	740	855	8,892	855	2,812
A018	Product 18	15-Oct-22	29.00	740	700	2,820	300	21,460	20,300	81,780	8,700
A019	Product 19	15-Oct-22	32.00	150	945	740	225	4,800	30,240	23,680	7,200
A020	Product 20	01-Aug-22	36.00	735	1,110	2,625	2,600	26,460	39,960	94,500	93,600
A021	Product 21	01-Aug-22	0.17	2,340	1,110	3,240	6,600	398	189	551	1,122
A022	Product 22	01-Aug-22	1.05	700	300	2,820	300	735	315	2,961	315
A023	Product 23	01-Aug-22	3.90	945	740	740	225	3,686	2,886	2,886	878
A024	Product 24	01-Aug-22	1.00	1,110	225	2,625	2,600	1,110	225	2,625	2,600
A025	Product 25	01-Aug-22	1.35	1,110	1,110	550	19,200	1,499	1,499	743	25,920