Excel PowerPivot producing pivot with blank columns

[masplin]

Hi

I have a set of booking data with a start and end date of each booking like this

I wanted to calcuate which day the unit is actually booked so as to work out utiliasation. I used this measure

Booked Days:=FORMAT(
SUMX(ADDCOLUMNS('Park Data',
                                              "Days",
                                              VAR  MinD=CALCULATE(MAX(MIN(DateTable[Day]),MIN('Park Data'[Start Date])))
                                              VAR  MaxD=CALCULATE(MIN(MAX(DateTable[Day]),MAX('Park Data'[End Date])))
                                              RETURN
                                              IF((MaxD-MinD)*1>=0,(MaxD-MinD)*1+1,BLANK())
                                               ),
                [Days]),"General Number")

This works perfectly except that the pivot is showing blank columns for every booking even if they have no days for the dates selected on the rows from my date table. e.g booking 1551 is for 4/4/127 to 8/4/17.  This means my pivot is uneccessarialy enormous.  I've checked the pivot settings and "show items with no data on columns" is ticked off

Any advice appreciated as not come across this before as default is susually to not display data that is blank?? assume something in my measure is causing this

Heres a link to small version  Bookings 

Thanks

Mike

[Ashish_Mathur]

Hi,

That link takes me to a sign-in page.

[masplin]

Try this one

https://www.dropbox.com/s/k2dm0vo91vmoudd/Largo%20Analysis%20test.xlsx?dl=0 

[lbendlin]

It's not clear what your expected outcome is. Please elaborate.

[masplin]

Typically in a pivot table the default behaviour is if the answer is null then the row/column is not displayed unless you specifically change the properties.  You can see that booking 1551 does not have any results for this date range so the column should not appear.  Maybe its the format as "General Number" causing it?

I have sort of fixed it by wrapping the whole measure in an If([Measure]=BLank(), Blank,[Measure]), but seems odd and inefficient to have to add this step.  It seems that the SUMX part when adding BLANK() does not produce a BLANK unless you force it to evaluate it as such.

[masplin · Accepted solution]

Ok I've found it. Seems that formatting a BLANK as "General Number" means it is not being treated like a normal BLANK.  I took out the FORMAT part and works as normal now.  Seems odd that a formatted BLANK is not a true BLANK.  I only added this as was having issues with the measure producing a date not a number. 

[lbendlin]

That is working as designed. Applying FORMAT to  a blank result will yield an empty string which is indeed different from BLANK((.