Fulfill null values with zeros based in a condition

[jessicarocha]

The customer can order some personalized products (IDNR = product number). When he does that, we agree to a minimum quantity he needs to order every year (Jahresmenge lt. ENK).  I would like to visualize if the customer fulfill the agreement or not. The actual quantity he ordered (Menge) and how much it is missing to reach the annual quota. 

The problem is that when he does not order in one specific year, I don't have a zero as ordered quantity in this year. Therefore, I can not calculate how much it is missing for reach the quota. 

In the picture is an example of what I would like to achieve. 

I only wants a "0" in Menge in the Business Years that the product exists.
So, for product 06678984, it should show exact this because in 2018/19 the product still did not existed. For this year, the field should be null. The product was created in 27.10.19 and this is business year 2019/20.

However, product 0777898 was created in BY 2018/2019. So, for BY 2021/22 it should show a 0 in Menge. The customer has not ordered anything yet. He still needs to order 5000 for reach the quota. 

Can you help me to do this? 
There is a dummy dataset in google drive. 

https://drive.google.com/drive/folders/1waRsOLMWQIhyJasxtPSCt2bYq_9Ucv_z?usp=sharing 

PS: Business Year starts from 01.07/xxxx and goes until 01.06/(xxxx +1) 

ex) product created in 27.10.2018 is Business Year 2018/19

Thanks in advance!

[lbendlin]

Your data model is missing a calendar table. Without that you cannot figure out which fiscal year the product was introduced in.  What should happen when the product was introduced on the last day of the fiscal year?  Shouldn't you prorate the quota for that?

[jessicarocha]

@lbendlin I do have a calendar table in the model. It is connected by the fiscal year and not the creation date.

However, the data I get is already aggreagated. It shows me the customer's order per business year.
For example, like product 0003 from my table bellow, the product is created in 01.08.2018, therefore, fiscal year 2018/2019. If the customer does not order anything in 2019/2020, this entry does not appear with a 0 and it should...

I also thought about making a cartesian product and make sure that all products have all fiscal years, then replace null with zeros. This approach does not work because it would appear fiscal year before the product was actually created and that is how I don't know how to proceed...

Current Table:

Material (fk)		Creation Date		Order Qty		Target		Business Year (fk)
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

I am doing all of that because the end goal is to see if the customer reached the agreed target. Like the visualization in my original question.
Thanks again for taking the time to help me out!

[lbendlin]

Your calendar table needs to have day level granularity to be usable. The fiscal year column is an important part but it cannot be used for the linking . The link needs to be on the date 

[jessicarocha]

@lbendlin That's a good point. But in this case, i don't think it solves my problem....

Now I can see how the products were created per month or quarter but this is not important for this report.
What I need to see is that once the product was created, for all the business years following, it should always have a order quantity, even if it is 0. 
Instead, I continue to see order quanttiy only for the years that the customer ordered something. The other years are missing....

[lbendlin]

You're getting there. 

Ditch the Dim Fiscal Year table - you don't need it any more.

Change the Dim Eroffnüngsdatum table name (nice typo by the way)  to just Dim Dates and mark it as a Dates table.  That table contains your Fiscal Year/ Wirtschaftsjahr column, and you can use that in your visuals.  You can use measures to calculate if a certain product was active in a certain fisal year.  Be very precise about it though.  What should happen if a product was activated on the last day of the fiscal year?  Does it still count as active for the entire fiscal year? 

[jessicarocha]

@lbendlin thanks for the tips! I did all that now 😄 

The thing is, the latest product creation date that I have, it is related to the last fiscal year (2020/2021). But I do have orders already in the fact table for the current business year (2021/2022). So I would be missing one filter here. 
What would you advise then? To manually add one row in the date dimension table with a fake creation date corresponding to the current business year? 

Regarding your question: yes, let's say that if a product was activated in the last day it still counts as active for the entire fiscal year. 

[lbendlin]

In your measure for the decision if you want to count the product or not you now say  (meta code)

YEAR(product intro) <= Year (Calendar[Date])