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 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.

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    Hi Anonymous 

     

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

     

    • 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?