Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |