Impossible Date Situation

[Anonymous]

I have a table with users and 2 date fields that I unpivoted to make the date fields a single date field:

I then created a date table to handle these dates and created a relationship:

Then, I duplicated my main table above and pulled in 2 fields from it:

Established relationship between this table and my main table:

So from here, if I total my YTD_Producer_Goal__c field with no graphing, the total is $4,482,000. I then tried to turn this into a monthly value, so $4,482,000/12 which is $373,000 for each month. However, when I attempt to calculate this accumulatively and place it on a graph, I do not get that number each month and my grand total falls short by the end of the year:

So there's only 3 months where I have the correct amount. When I did some digging around into why, I discovered it's because not every user has dates from every month associated with them, or in other words gaps in their dates.

For instance, one user has a YTD_Goal of $40,000, so that would be $3,333.33 monthly. Above in December on the graph, we see the final number is $4,442,000 instead of $4,482,000 which is a difference of $40,000. This user is the only user that does not have any December dates in their data, so their grand total total isn't accounted for on the graph in December:

So my question is, how can I get to a point where a user's expected value for a month shows even if they do not have any dates associated with that month yet? I know that in my main table if a December date finally populates for that user, the total on the graph would display correctly for December, however I've learned that not every user will have data every month which is why every month doesn't = $373,500 like I thought it would.

[v-eachen-msft]

Hi @Anonymous ,

You could try IF() function to judge if the result is blank. Just like there were no results in December, you could use the value of the previous month.

[Anonymous]

hi @v-eachen-msft , thank you for your reply. I think I understand the gist of what you're saying! So basically convert this into a measure: "If a user no dates for the month of ____, use the previous month number."

[jthomson]

You asked this last week and got a reply?

[Anonymous]

@jthomson it didn't solve my issue?