Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jessicarocha
Helper IV
Helper IV

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

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 YearCompany IDProduct IDQtyDate
201301250097858697101/1/2013
20140225009785869752/1/2014
20150125009785869751/1/2015
201502250097858697102/2/2015


Power BI standard visualization: 

jessicarocha_0-1672836292143.png
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.

jessicarocha_1-1672836341914.png

 

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. 

 

jessicarocha_2-1672836611733.png

 

In the link below you can find a DUMMY example.

Dummy Power BI 

Thank you very much.

 

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

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() 
)

Connect on LinkedIn

View solution in original post

4 REPLIES 4
DimaMD
Solution Sage
Solution Sage

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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
Community Champion
Community Champion

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() 
)

Connect on LinkedIn

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.