Forum Discussion
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 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 Graph
Does anyone know how I can show the blank values as zero between to dates without creating a new date table?
Kind Regards,
4 Replies
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)
Hi Anonymous,
Another solution is enabling "Show items with no data". Please refer to the snapshot below.
Best Regards,
DaleHi 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:
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?
