Date Table - Generated

[Highlander_]

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.

[shebr]

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.   https://youtu.be/vJ7xNUK8sQE

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

Thanks

shebr

[Highlander_]

Hi, 

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.

[Highlander_]

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?

[Highlander_]

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

Kind regards

highlands