Rolling chart need to show 0 if blank during rolling date range

[mdrew9]

I've defined a measure which gives me a chart of the forecast for the next 4 months based off todays date. The problem is if there is no forecast, the pivot table shows nothing. These are schedule items so there is nothing that exists not the value is 0. I've tried to modify the formula and it gets rid of my 4 month filter and shows 0 for every month of the year. Any ideas?

Not Started:=calculate('Schedule'[Notstarted],FILTER(all(BasicCalendarUS),BasicCalendarUS[DateKey]>='Schedule'[Start of Period]&&BasicCalendarUS[DateKey]<='Schedule'[End of Period]))

Start of period = eomonth(today(),-1)+1 or the first date of the current month.
End of period = start of period + 4

Notstarted is just activities which are complete.

 

[mdrew9]

For example my data has no activities in April. So my data goes Feb, March, then nothing. I need it to show April with 0.

 

[MD610]

Try wrapping your [Not Started] measure with an IF statement:

New Measure:= IF(ISBLANK([Not Started]), 0, [Not Started])

Use the new measure in your pivot.

You could use this same IF statement format directly in your [Not Started] measure, but then you have to repeat all that twice in the IF statment. It becomes very hard to read and trouble shoot for syntax problems trying to do it all in one.

 

[mdrew9]

Strangely that solution makes all dates in my date table show up in the pivot....?

 

[MD610]

I guess I don't understand your problem fully then. From the initial description, I thought you did want to show 0 everytime there was a blank value from your measure.

What do you want your pivot result to look like?

Is it just Month names in the rows or is there another field being used in rows?

 

[mdrew9]

the data values are a count of activities. The column values are Month names, and rows are the selected activity. I have data from 9-2012 to the end of the project. But per this filter I want only 2-13 through 5-13 to show up. I want to see Feb, Mar, Apr and May regardless of if there are any activities that month, I want to show 0 in those months. Currrently without the if blank I show February and March as there are no activities in April and May. When I put that if blank section on I show dates 1-1900 - 12-2100 whenever my date table ends.

 

[MD610]

Ok. Try putting the IF statment inside your original measure like so:

Not Started:=calculate( IF(ISBLANK('Schedule'[Notstarted]),0, 'Schedule'[Notstarted]) ,FILTER(all(BasicCalendarUS),BasicCalendarUS[DateKey]>='Schedule'[Start of Period]&&BasicCalendarUS[DateKey]<='Schedule'[End of Period]))

Ideally, you want to try and get your date table to match the range of your data set. So whatever the lower date range of your data is, that is where your date table should start and it should go to the upper date range of your data. So if your data only spans Mar 2011 - June 2011 then your date table should usually be from 3/1/2011 - 6/30/2011 (or whatever specific date limit is in your data set).