how to handle three different order date forecast

[SelflearningBi]

Model Name	Color Name	MOQ Color	MOQ Model	Order Date 1	Order Date 1 Forecast	Order Date 2	Order Date 2 Forecast	Order Date 3	Order Date 3 Forecast	Order Date 4	Order Date 4 Forecast
A	Red	1000	300	1/1/2022	1000	1/3/2022	3000	1/1/2022	1200	1/1/2022	1000
B	Red	3000	1000	1/2/2022	1000	1/5/2022	1000	1/2/2022	1000	1/6/2022	3000
C	Blue	5000	3000	1/1/2022	2000	3/1/2022	2000	1/1/2022	5000	1/11/2022	2000
C	Pink	1000	3000	1/1/2022	5000	3/1/2022	500	1/1/2022	1000	1/11/2022	2000

Similar Data as above table

I would like to analyze different order dates if meeting the MOQ Model level and MOQ Color level.


Color Level

In MOQ color level order date 1 in Model A : ( 1000 -1000),  YES meet MOQ, IF ORDER DATE FORECAST HIGHER THAN THE ACTUAL LEVEL THAN IT MEET THE MINIMU ORDER QTY, and should add a column display it meets or not meet. 
In MOQ color level order date 1 in Model B : ( 3000 -1000),  NOT meet MOQ
In MOQ color level order date 2 in Model A: (1000 -3000),  YES meet MOQ

Model Level
In MOQ Model level order date 1 shoud add the same model together. 
Takes Model Name C In order date 1 as an example:  
                         (6000 - 7000), Yes meet MOQ 
Order date 2 will also do the same thing  (6000 - 2500),  No meet moq     
               
I have to do the same thing for all 4 order dates, are there any methods I can easily fuifill this calcuation? 

[v-easonf-msft · Accepted solution]

Hi, @SelflearningBi 

Before calculating the difference, you can unpivote the date columns, which saves you from creating multiple similar measure for each date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKTQGShgYGBkDKGEwa6hvqGxkYGSHEDfWNYSLGBhhqjLDqitWJVnKCWwDTBtNthGGDKYYIphozFFeAbHAGcpxySlOBlCncD2jOMYJKYIggqTGFi6AqgtkRkJmXjRJO2PUbo4jgCEt0K2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Model Name" = _t, #"Color Name" = _t, #"MOQ Color" = _t, #"MOQ Model" = _t, #"Order Date 1" = _t, #"Order Date 1 Forecast" = _t, #"Order Date 2" = _t, #"Order Date 2 Forecast" = _t, #"Order Date 3" = _t, #"Order Date 3 Forecast" = _t, #"Order Date 4" = _t, #"Order Date 4 Forecast" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model Name", type text}, {"Color Name", type text}, {"MOQ Color", Int64.Type}, {"MOQ Model", Int64.Type}, {"Order Date 1", type date}, {"Order Date 1 Forecast", Int64.Type}, {"Order Date 2", type date}, {"Order Date 2 Forecast", Int64.Type}, {"Order Date 3", type date}, {"Order Date 3 Forecast", Int64.Type}, {"Order Date 4", type date}, {"Order Date 4 Forecast", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Model Name", "Color Name", "MOQ Color", "MOQ Model", "Order Date 1 Forecast", "Order Date 2 Forecast", "Order Date 3 Forecast", "Order Date 4 Forecast"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Order Date"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Forecast", each if [Attribute] = "Order Date 1" then [Order Date 1 Forecast] else if [Attribute] = "Order Date 2" then [Order Date 2 Forecast] else if [Attribute] = "Order Date 3" then [Order Date 3 Forecast] else if [Attribute] = "Order Date 4" then [Order Date 4 Forecast] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Order Date 1 Forecast", "Order Date 2 Forecast", "Order Date 3 Forecast", "Order Date 4 Forecast"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Forecast", Int64.Type}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Model Name", "Color Name", "Attribute", "Order Date", "Forecast"}, "Attribute.1", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns1",{{"Attribute.1", "MQQ"}, {"Value", "MQQ Value"}})
in
    #"Renamed Columns1"

Best Regards,
Community Support Team _ Eason