PowerPivot Measure Formula that will return Blank

[fr650008]

Hello,

I am wondering if someone can help me. I've been looking at other forums online but I cannot find a similar problem. I am creating a burndown chart in PowerPivot with data linked to SQL server. The actual burndown data is in a table that links date of completion to a calendar and uses the following measures formula to calculate the burndown line: =(CALCULATE([Total WO Count], All(DateTable[Date])))-([Actual WO Complete](DATESBETWEEN(DateTable[Date], BLANK(),LASTDATE( DateTable[Date])), All(DateTable)))


However, once the date is greater than today I get a line in my chart that is a straight line reaching to the end of the calendar. I need to return blank for when the min value for actual WO complete is reached. Can anyone help? I've tried using If and Min in separate measures that reference the above measure but that is not working for me and I'm not sure why.

 

[tjdurkin]

Hi fr,
Maybe something like =if(datetable [DATE]>=TODAY (), BLANK (), [yourmeasure])

 

[fr650008]

Thanks for your help, TJ. I tried the formula and I'm getting this error:

Calculation error in measure 'WorkOrderViewBurndownTable'[Measure 1]: The value for column 'Date' in table 'DateTable' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

 

[XLBob]

It's so hard to figure out what's going wrong without looking at your workbook.
Looks like there is something wrong with your formula. The last all(datetable) is basically overwriting your datesbetween, try remove it.

 

[tjdurkin]

Hi fr,

...This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column...

So, in attempting to answer one of my first powerpivot questions, I broke one of the golden rules of measures/calcfields by not aggregating... brutal.

how about:

=if(max([Date])>=today(),blank(),[[I]yourmeasure[/I]])

I tried this with the following example data and got it to sum everything with a date < today().

Thinking if it works for sum it should work for your measure. I was also looking at the datesbetween() function that XLBob mentioned. Maybe feeding the function Today()-1 as the End_date parameter?

Measure 3:=calculate([vmSUM],(DATESBETWEEN(Table1[Date], BLANK(),today()-1)))

 

[fr650008]

The if statement worked! Thanks so much TJ for your help! And thanks for your help too Bob!

For anyone's future reference I tried changing the measure and it gave a straight line for the value of today... Haven't figured out why - it doesn't seem to make sense to me.