Problem with graph with forecast calculation per week and month

[MiKeZZa]

Hi all,

I have a problem which in my opinion can't be solved but maybe someone here has a brilliant idea, so I will give it a try....

What I have as dataset (prepared in SQL): 

• DateID
• EmployeeID
• Function (1 person can have multiple functions so this makes it that my records duplicate, but this is al fixed good in SQL)
• Costcenter (1 person can be active on multiple costcenters so this makes it that my records duplicate, but this is al fixed good in SQL)
• totalnumberofincidentsinweek
• FTE_Total
• totalnumberofincidentsinweek_oncostcenter
  
• Numberofdays; displays number of days in week; generally 7, but at begin or end of month less (as example week 9 of 2017; it has 3 days in 2017-02 and 4 days in 2017-03 so I split it in 2 records)
• Numberofdays_rato; numberofdays but multiplied with FTE factor
• LenghtOfIncidentInDays
• NumberOfIncidents
• NumberOfIncidentsInWeek
• Source (contains 'Actual' or 'Forecast')

This contains data untill last month. For current month I do have number of incidents, but no information about the paid salary (that comes at the end of month), so I can't calculate this. What I do in SQL is making a forecast about which salaries/FTE are going to be paid (this trick is not part of the question; it works for my situation). So the dataset contains (by example) info untill Februari 2017 and March is going to be forecasted. This all works fine. After that I'll split it in to weeks (as info is presented per week). This also works fine.

But now comes my problem: I have at this point a good working dataset untill Februari 2017 (so it includes a part of week 9). And now I want to present this info in PowerBI in 1 linegraph this way:

(only the forecast period will be shorter; it is 1 month).

But generally this doesn't work with my dataset as described. My dataset will generate a good actual line and 1 dot for forecast. This because of that forecast has 1 value. So what I'm making now in DAX is that when my actual ends at Februari 2017 and my forecast starts at March 2017 that I'll duplicate the 'Actual' value of Februari as 'Forecast'. This works fine when I'll look at month value. But my graph gives users the ability to drilldown to week level and this goes wrong.

When Februari contains 6.5% actual I can also fix 6.5% for Februari forecast without a problem. But week 9 of 2017 will contain actual 6.8% and my 6.5 duplicate is then splitted into parts per week which are all 6.5%.

So ideally I'll be able to create 6.8% forecast for week 9 in March (which is 4 days) and create for the other 27 days of March a percentage that calculates to 6.5% in total. So this must be: (6.8 * 4) + ? * 27 = 31 * 6.5 -> this gives 6.455 for the other days.

But I think this can't be done in DAX, but maybe I'm wrong....

[v-qiuyu-msft]

Hi @MiKeZZa,

Would you please share the sample data and desired results, so that we can try to analyze whether the requirement can be achieved?

Best Regards,
Qiuyun Yu

[MiKeZZa]

Hi, as you can see in these PBIX file: download I have a graph (I hope it's not to hard because of the language...).

What I have in my dataset from SQL is:

March 2016 - Februari 2017 = actual info

March  2017 = forecast info (calculated)

When I plot this in the graph it doesn't become 1 long line, but a line for actual and a dot for forecast (because it's just 1 value). So I want to duplicate March 2017 als Februari 2017 info with source (herkomst) calculated.

That's what I'm doing in the calculated table. And that worked perfect when I had a dataset with as lowest level month. But now I've introduced week (see graph, you can drill-down) I can't make it to work nice so that the line doesn't have the small gap (for month AND week).

I hope it's a little more clear now?

[v-qiuyu-msft]

Hi @MiKeZZa,

>>When I plot this in the graph it doesn't become 1 long line, but a line for actual and a dot for forecast (because it's just 1 value).

In the line chart, I see you have placed [herkomst] column in Legend property, line chart values will be categorized based on [herkomst] values. If you want to plot one line. Please remove the [herkomst] from the Legend.

Best Regards,
Qiuyun Yu

[MiKeZZa]

Yes, clear. But I want to show which part of the lines is real and which part is forecasted. So I'm looking for a solution which solves it's in de duplication of the data.