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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Handling blank values between dates

Hello,


I've got a problem with handling blanks between two dates. 

I've got a measure that sums up the amount of hours in my fact table. This is filtered by a relative date slicer.

The picture below shows my actual result.

Actual TableActual Table

The months that are blank are not shown in my graph.

I made the desired graph bycalculating the total hours for each month in SQL.

I then loaded that data in my report as a second fact table.

This seems to fix my problem but then I can't highlight extra information in the graph unless I add those desired values in my SQL statement.

 

Desired GraphDesired Graph

Does anyone know how I can show the blank values as zero between to dates without creating a new date table?

 

Kind Regards,

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have fixed my issue by not using a date hierarchy but just a calculated column with year and month instead.

I could then use the "show items with no data" option.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

I have fixed my issue by not using a date hierarchy but just a calculated column with year and month instead.

I could then use the "show items with no data" option.

Anonymous
Not applicable

Hi @Greg_Deckler and @v-jiascu-msft,

 

I have tried both possible solutions. But it does not seem to work.

 

With both solutions I get the following result:

Capture.PNG

It seems like my date dimension does not filter properly even though I've got a slicer on it and the relation setup between both date columns in the tables.

 

I cannot create a different date dimension using

Table = CALENDAR(date(2018;1;1);Date(2019;1;1)

since both values are variables depending on the users choice. I cannot simply say that the start date is in 2018 since it can also be somewhere in 2019. The same goes for the end date.

 

Is there an alternative?

v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

Another solution is enabling "Show items with no data". Please refer to the snapshot below.

Handling-blank-values-between-dates

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Community Champion
Community Champion

Many times you can fix this by moving your calculations to VAR's and then have a return RETURN like:

 

IF(ISBLANK(__SomeVar),0,__SomeVar)



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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