Seeking Help about Power BI Line Chart & Forecasting Options

[MorshedHemal]

Hello Mates,

Good day to all of you. I am facing some strange problems in my power BI projects & needs solutions & suggestion to resolve those problems.

Problem Background :
1. I am maintaing a folder where Day wise Sales Target & Achievement data gathered. The files are in normal excel file format & each file is for a individual years where it contains every bit of sales & target details for each and every day for that year. 

2. From those files, i created a combined power query file in power bi where in a single table, it is showing all the target & sales details data by day wise from the year 2017 to 2024
3. Problem 1 :  when i am creating a day wise Line chart, it is showing Zero Values (when sales is close for the whole day). I tried to change the numbers in excel file like when value is equal to zero, then show as " ".  That formula removes the problems but then another problem arise which is sales amount is showing for the upcoming days. How to resolve this problem? (picture attached)

4. Problem 2 : in this line chart, i am trying to use forecast options of power BI to predict the future sales for next 30 days with 10 points on seasionality & 90% confidence level. It is showing forecast data for the upcoming days of the year 2024. But when i change the year from 2024 to 2023 by using the year filter option, then again it is also generating forecast for the year 2023 for the same period where  actual sales data already available in the file. I am seeking expert suggestion to resolve this problem. (picture given below of the problem)

Line chart with Forecast (white line) for the year 2024 where yellow lines showing daywise actual sales of this yearit is showing forecast & sales which starting from 27 Aug where no sales happened of the year 2024But when i changed the Year from 2024 to 2023, forecast line also showing for the last year where actual sales already available. Forecast only need to work for the future dates, not for the past dates which is already gone

[lbendlin]

but then another problem arise which is sales amount is showing for the upcoming days. 

You lost me on this one.  How is that a problem?

But when i change the year from 2024 to 2023 by using the year filter option, then again it is also generating forecast for the year 2023

The forecasting function has no idea what "2024" or "2023"  means, All it does is look at the data you give it and then apply its algorithm.   It's actually cute to see that the forecast closely matches the actuals.

[MorshedHemal]

About problem 1 : the scenario is there is no data in the excel file for the date range 27 Aug to 31 Aug 2024. When i use that column in Power BI as "Sum of Sales", those date range sales total showing as "0" in the line chart. Now, i am manupulating the excel file like this way- " if the day total is >0, then it will show the total amount of that day, else it will kept as " " (blank) for that day. When i implay that logic in the excel file for the rest of the days, it is perfectly working in the line chart for the previous days data like 3/10/17 Aug where there is no sales for the day & "0" sales day skipped in the line chart. But for the future / upcoming days, it is showing sum of sales in a flat line where actually the excel file contains the data as " ". I hope you are clear now.

about problem 2 : forecast means prediction of future, not about the past mate. If forecast options consider the data of past times, then what is the meaning of using it? It should only showing the forecast about the upcoming days, not for the days which is backdated from the present dates. I thought there might be a way around to resolve this. 

[lbendlin]

note that " " is not the same as BLANK().  Your source data really needs to have Nulls, not single space strings.

"forecasting is hard, especially the future".  Don't use that feature for past dates then.