Is it possible to add new rows in the dataset based in a codition?

[jessicarocha]

The customer can order some personalised product. When we acept the order, there is a commitment to order every year a minimum amount. I would like to check which customers stick to the agreement. 

So I created a metric to say how much is missing to reach the target (Order Qty - Target), for example. 

However, in some situations (Material 0002 and 0003)  no quantity was ordered in a specific business year. In these cases, there is no target. The row is not created. 

Thus, I would like to create a row based in a condition.
After the creation date, it should always contain a value for all business years following. In case there is not, create a new row whrere the values are copied from the previous row, except the Order Qty column has 0 as a value.

Current Table:

Material		Creation Date		Order Qty		Target		Business Year
0001		01.08.2019		100		150		2019/2020
0001		01.08.2019		150		150		2020/2021
0001		01.08.2019		300		150		2021/2022
0002		01.09.2020		590		500		2020/2021
0003		01.08.2018		900		1000		2018/2019
0003		01.08.2018		1300		1000		2020/2021

Want to add (what is in bold):

Material		Creation Date		Order Qty		Target		Business Year
0001		01.08.2019		100		150		2019/2020
0001		01.08.2019		150		150		2020/2021
0001		01.08.2019		300		150		2021/2022
0002		01.09.2020		590		500		2020/2021
0002		01.09.2020		0		500		2021/2022
0003		01.08.2018		900		1000		2018/2019
0003		01.08.2018		0		1000		2019/2020
0003		01.08.2018		1300		1000		2020/2021
0003		01.08.2018		0		1000		2021/2022

Is it possible to do that?

I think that an index column in the business year would help, but I have no idea how to do this.

Business year goes from 01.07.xxxx to 01.07.xxxx+1   -> Business year (xxxx/xxxx+1)

Thanks a lot for the help!

[v-kkf-msft · Accepted solution]

Hi @jessicarocha ,

Please try the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc1RCsAgDAPQu/RbNKkI9izi/a+x1bmhMH8CKY+0NQFACQJG1Kig3YWAZ/H0U1IopIeDLqtWuOZR521b6VpfrVNbHB+DFBuJ3+28bte72LONyVnTeHrizLv/5vsF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, #"Creation Date" = _t, #"Order Qty" = _t, Target = _t, #"Business Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", Int64.Type}, {"Creation Date", type date}, {"Order Qty", Int64.Type}, {"Target", Int64.Type}, {"Business Year", type text}})
in
    #"Changed Type"

let
    Source = Table.SelectColumns(Table,"Material"),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Table.AddIndexColumn(Table.Sort(Table.Distinct(Table.SelectColumns(Table,"Business Year")),{"Business Year"}), "Index", 1, 1, Int64.Type)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Business Year", "Index"}, {"Custom.Business Year", "Custom.Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Material", "Custom.Business Year"}, Table, {"Material", "Business Year"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Creation Date", "Order Qty", "Target"}, {"Table.Creation Date", "Table.Order Qty", "Table.Target"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table",{"Material","Custom.Index"}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Material"}, {{"All_Rows", each Table.FillDown(_,{"Table.Creation Date","Table.Target"}), type table}}),
    #"Expanded All_Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All_Rows", {"Custom.Business Year", "Custom.Index", "Table.Creation Date", "Table.Order Qty", "Table.Target"}, {"All_Rows.Custom.Business Year", "All_Rows.Custom.Index", "All_Rows.Table.Creation Date", "All_Rows.Table.Order Qty", "All_Rows.Table.Target"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All_Rows", each ([All_Rows.Table.Target] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,0,Replacer.ReplaceValue,{"All_Rows.Table.Order Qty"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"All_Rows.Custom.Business Year", "Business Year"}, {"All_Rows.Custom.Index", "Index"}, {"All_Rows.Table.Creation Date", "Creation Date"}, {"All_Rows.Table.Order Qty", "Order Qty"}, {"All_Rows.Table.Target", "Target"}})
in
    #"Renamed Columns"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[jessicarocha]

Hi @v-kkf-msft. Thank you so much for your help! That is exactly what I need.

It does run a bit slow because my dataset is big. So I will make a request for this problem to be changed in the database directly but until then, thanks to you, I get to run my report. 

I really appreciate it! 

Kind regards, 

Jéssica

[mattww]

I wouldn't approach this by adding dummy data into your dataset, it seems more like a data modelling problem.

The Business Year is best held in a separate calendar table, then you have access to all the years regardless of whether there are actual order details for that year. 

When creating a measure for order quantity, you can do something like below, where the +0 would force a value of zero where there are no records.

 =SUM([OrderQty]]) + 0

[jessicarocha]

@mattww I do have a dimension only with the business year. But if in the fact table that business year does not appear (like for product 0003 there is no entry for fiscal year 2019/2020), I can not force the zero, right?

I could do a cartesian product and add all fiscal years in the fact table and then replace the nulls with zeros. But this would be wrong because I would add all fiscal years available in the dimension fiscal year. Thus, I would also have fiscal years in the fact table before the product was actually created. And in this cases, it would be wrong to have a 0 there....

[mattww]

Hi @jessicarocha , you should be able to force a zero, see sample linked below.

https://easyupload.io/sqdgzm

The outstanding bit is how to get your target so it would be useful to understand how this number is generated, for example is it an annual target, does it depend on the customer/product?

I would think a second fact table with your targets, linked to the relevant dimensions would probably do the trick, rather than storing the target on your transactional fact table

[jessicarocha]

Hi @mattww ,

I can't open your link due to some security layer of where I work. I will try later at home.
The new fact table seems like a good idea. You understood it correctly, the target does depend of the customer and the product.

However, I still don't see how this will solve the missing fiscal years in the transactional fact table...

[mattww]

Ah sorry about that, unfortunately I can't upload .pbix files on here (I think it's only available at a certain rank). If you continue to struggle to get it, direct message me and can send it directly.

In the meantime, here are some screenshots which might better illustrate what I mean

So for your second Fact table, you would probably include a DateKey for the start of the fiscal year the target relates to, as well as a CustomerKey and Product Key. As your Actual and Target Facts would share the same Date, Customer and Product dimensions, this should allow you to combine the two facts in the same visuals.

[jessicarocha]

@mattww thank you so much for the screenshots. 

I followed your explanation step by step since I can't open the file. I did everything like the screenshots. 

The problem is, that by doing this, I have 0s for all Fiscal Years available in my Calendar Dimension. 

For example:
Product 003 was created in 2018/2019. I do get a 0 for 2020/2021 and 2021/2022. That is correct. 

However, in the case of product 002, it is not correct.  This product was created in 2020/2021 year, so I should only get 0 for the future years, like 2021/2022. Instead, I also get 0 for year 2018/2019 and 2019/2020 when the product still did not exist. In this case, it should be null and not 0...

Do you know what I mean?

Thanks again.

Kind regards, 

Jéssica