Latest Enterprise DNA Initiatives

Date Slicer is Giving Blank Value

I have created a date table using the MCode. The start date and the end date exactly match the start date and end date of the data set.

When I created a slicer prior to creating a relationship, it had no blank. But as soon as i created a relationship and sales table, - Slicer immediately added a Blank value to the slicer.

Any Ideas on how to resolve this, please?

@jps Your date table isn’t complete, the upper bound and the lower bound of the Sales table exceeds that date table that’s why the DAX Engine is adding Blank row to the Date table.

I did re-check that, but the date table I created has exact dates as in the Sales table that I have.
Could this have something to do with the FY year start?

@jps The concept is straightforward - the only reason why Blank row is added is because of the invalid/missing relationship, if you have rows on many side for 1/1/2021 (for the column that participates in the relationship), then it is imperative to have that date in the Date table, otherwise it will show blank.

That makes complete sense. but somehow here I am, stuck with this small thing, missing out on something very simple. I have attached my PBIX file, if you could take a minute to see what I am overlooking

Thank you in Advance

MyAttempt.pbix (4.5 MB)

@jps Your Date table is missing 12/31/2018.

image

1 Like

Great That resolved it. Actually, just before sharing the file with you, I had noticed this and recreated the table, and assumed that was fixed. I didn’t know that to have for example 31st December as the end date I needed to select 1st January in the function(query).

Thank you so much. - I have learned an important lesson :slight_smile:

1 Like

@jps ,

This probably has something to do with your FY start date. It starts in April, so you need to have a complete FY worth of dates in your Date Table for it to work properly for DAX calculations. In your example, just try adding dates all the way through 2019, and it should work properly.

Thanks
Jarrett

1 Like

Sure - Ill keep an eye on that too.
As @AntrikshSharma pointed out that my date table had last date of 31st December 18 missing. I have fixed that and the blank button in slicer has now gone.

Thank you for highlighting the Financial year aspect too. Ill keep an eye on it and if required will update it.

Thank you

@jps Awesome! I would say always create Date tables from 1/1 till 12/31, add or subtract an year or so if you have a different fiscal year but having the aforementioned calendar will work in any scenario.

2 Likes