Assign Missing Dates With Zero for Value

[Anonymous]

Hi,

I have a list of [Projects] each project has a [Date] and a [Value].

The dates are by month, eg. 2014-12-01

Some of the projects don't have any data for some months. I need them all to show all 12 months of each year back to 2014, and just display a value of 0 for the months that didn't contain data.

I can't think of a way to do this easily.

[v-diye-msft]

Hi @Anonymous 

Perhaps you need the IF function to replace the missing date with 0.

[Anonymous]

@v-diye-msft 

@amitchandak 

So I tried creating a date table, but I'm not sure how I would merge that with the projects table to get the missing values in there.

Here is my DAX for the 3 month moving average measure:

3 Month Average = 
CALCULATE(
    AVERAGEX( projects, projects[Value]),
    DATESINPERIOD(
        projects[Date],
        LASTDATE(projects[Date]), -3, MONTH
    )
)

Is there a way I can build in an IF statement that says If there is no value for a month, use a zero rather than the next available month? Right now, if there was no data for July or June it would calculate the average as (August+May+April)/3. I want it to do (August+0+0)/3.

[v-diye-msft]

Hi @Anonymous 

Why don't try to replace the null with 0 in query editor?

[Anonymous]

@v-diye-msft 

There are no nulls. If a record was not created for the project on that day, then nothing got recorded. This is in Dynamics btw.

[amitchandak]

Try this workaround, Create a Month year calendar.

1. If you join it, in formula use crossfilter 

2. If you do not join, then it should give you all  month