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
Highlander_
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.

4 REPLIES 4
shebr
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.   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

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.

 

 

 

 

 

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

highlands

 

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.