Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Assign Missing Dates With Zero for Value

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 al...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-diye-msft
Community Support
6 years ago

Hi Anonymous 

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.