Show no data for a date type without the entries in the fact table

[jessicarocha]

Hello everyone, 

I have a situation where in my fact table there are no entries for a given month. However, in the graph visualization, I still would like to see this months with 0 as a value. 

Relevant of information regarding my model:

Dim Date: contains a typical date dimension. Based on a interval (7/1/2012 - 12/31/2022), I have a continous date period in a daily basis.

Dim Product: contains information about the product.

Dim Company: contains information regarding the corresponding subsidiary.

Fact: contains the sales of a given product per month and company. 

Particularities:

• The fact table is aggregated per month. The column that identifies the month has a format YYYYMM. I then created another column with YYYYMM01 to be able to transform to date and connect with the Dim Date. 
• Not all products are sold every month. Thus, there are months "missing" in the fact table for a given product.

Example: 

Month Year	Company ID	Product ID	Qty	Date
201301	2500	97858697	10	1/1/2013
201402	2500	97858697	5	2/1/2014
201501	2500	97858697	5	1/1/2015
201502	2500	97858697	10	2/2/2015

Power BI standard visualization: 

I saw that there is the possibility to configure the graph to show items with no data. In order to that, I even filtered my Dim Date to only show the first of each month since the fact table is in a monthly bases and I did not want more 0s than required. However, this made no difference at all. The visualization was exact the same.

Then, i created a measure to fill in the places missing data: 

Qty with 0s = if (ISBLANK(SUM('Fact'[Qty])),0,SUM('Fact'[Qty]))

This worked better but still not as I want. I want to have the line draw only from the first month available in the fact table (July of 2012) until the last month available in the fact table (December 2022), independent of the product itself. 

Is it possible to add this condition in the measure? 

I am not sure how to do that. 

In the link below you can find a DUMMY example.

Dummy Power BI 

Thank you very much.

[DimaMD]

hi @jessicarocha Please review the attached file and tell me if this is your expected result

[jessicarocha]

Hi @DimaMD . Thank you very much for taking the time to help with my problem. I had already used the solution suggested by tex628. That's why I can't accept your reply as the solution. But thank you! 
You guys had the same idea for the formula. It definetely works! 

[tex628 · Accepted solution]

Hi @jessicarocha , 

Try this: 

Qty with 0s = 
VAR mDate = CALCULATE( MAX( 'Fact'[Date] ) , ALL( 'Fact' ) )
Return
IF( SELECTEDVALUE('Dim Date'[Date]) < mDate , 
SUM('Fact'[Qty]) +0 , BLANK() 
)

[jessicarocha]

thank you very much @tex628 ! This is exactly what I need.