Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Hi @Anonymous
Perhaps you need the IF function to replace the missing date with 0.
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?
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.
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |