Generate missing hour data

[Michael_Hart]

Hi all,

I'm trying to display a bar graph with the latest 24 hour data for warehouse data. The data we receive only has hours were there has actually been movement in the warehouse. 

I wish to display the entire 24 hours of data on the chart and for any hours that do not have data to have 0.

Any help is appreciated, thank you.

[anthonyj]

Hi @Michael_Hart ,

This code will loop through all the values from 0 to 23 and create a column called [Hour]. This will associate with your current column filling in the blanks.

Hours:
Load
0 + iterno()-1 as Hour
AutoGenerate 1
while 0 + iterno()-1 <=23;

Thanks

Anthony

[Michael_Hart]

Hi @anthonyj ,

Thank you for the reply, 

The code does create the 0-23 hour rows and I can see the association in the data model viewer. But as soon as I put in my measure the hours still disappear.  

Thanks,

[anthonyj]

Ah yes, the measure will suppress nulls in the dimension so there needs to be a record created where none exists.

If you want a quick fix you could try adding a statement in your measure like:

if(sum([To Warehouse] > 0, sum([To Warehouse], 0)

Or

In your load script you could add the missing hours to your table with a dummy value of "0" for Warehouse and Staging columns. Just be careful. If you're aggregating with counts then this will artificially add rows. If it's sums then it is okay.

After loading your data tables take the Hours data and concatenate it to the bottom like:

// Add this section below your table that holds Hours and your measures.

Concatenate

load

Hours,

0 as [Your warehouse column],

0 as [Your staging column]

Resident Hours

where not exists(Hours);

Drop table Hours;

Thanks

Anthony