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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User
Super User

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors