Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

5 REPLIES 5
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

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

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

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

Hi @Anonymous 

 

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

 

003.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

@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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.