Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Date Table - Generated

Hello, I am faily new to Power tools such as Pivot/BI etc.  I just wonderd if anyone could help with this one;

I have a relatively large data set that for some reason or another is missing data for the year "2011" - I generated my date table, which has generated dates for the missing period.  How can I handle this? 

I tried using the "Allnoblankrows" function when trying to calcuate standard sum measure as I assumed that this function would exlcude blank rows. 

Slicer is also a pain as it shows year (I use the date table to generate the slicer as standard practice).


There is the obvious option of simply deleting 2011 dates from date table, but I don;t want to do this, I just would like to try and handle this situation.

I created orignal sum measure with the allnoblankrows i.e.  x=calculate(data[field_x],allnonblankrows(Calender[date])).

My assumption was that this measure (structure) would trickle down into other measures.


Possibly thinking too hard about this or not enough... like to do things properly...any help here much appreciated.

thanks in advance.

Resolver III
Resolver III

Hi @Highlander_


There is a fantastic video that creates you a date table in seconds. What this allows you to do is set your start and end date and also let you amend at any time.



Give it a go, replace your current table if its only a date table and start a fresh, sometimes its the best way.






Very easy to follow with PowerQuery and customised as required, thank you for this - really helpful.   



I confess I am still curious about handling orginal problem with DAX - this must happen so often surely there has to be a way of using a function along the lines of Allnoblankrows, handling slicers too..   I feel it's the only way I am going to learn.






Just to add that use of time intelligence functions (use of dateadd function) hgihlights this problem, there has to be a workaround surely?

Any gurus?

Many thanks for taking the time to post this, I'll try this one out.

Kind regards




Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors