Forecast

[kevinedora]

Hi, all.

I'm trying to create a simple forecast based on this logic = Average Collection * the number of remaining days for the month

Please see below for the result I'm trying to create.

Note: Please limit the forecast to end of month of the last collection date. E.g. If the last collection is on 9/04/2023 then the forecast data should be up until 9/30/2023 only. Please advise. Thank you!

Date	Collection	Forecast
09/01/2023	8000	224250
09/02/2023	11000	224250
09/03/2023	9000	224250
09/04/2023	6500	224250
09/05/2023		224250
09/30/2023		224250

[123abc]

Thank you for clarifying your requirements. If you want to create a forecast based on the current month only, you can modify the formula to take into account the current month. Assuming you have data from January 2023, but you want the forecast for the current month (e.g., September 2023), you can use the following steps:

Assuming your data is in columns A (Date) and B (Collection), and you want to calculate the forecast in column C:

1. In cell C1, enter the following formula to calculate the total collection for the current month:

=SUMIFS(B:B, A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A:A,"<="&EOMONTH(TODAY(),0))

1. This formula sums the collections in column B for dates between the first day and last day of the current month.

2. In cell C2 (assuming your data starts from row 2), enter the following formula to calculate the forecast for each day:

=IF(A2="","",IF(MONTH(A2)=MONTH(TODAY()),C1/COUNTIF(A:A,">="&TODAY())*(1-(COUNTIF(A:A,"<"&TODAY())/DAY(EOMONTH(TODAY(),0)))),""))

1. This formula calculates the daily forecast only if the date in column A corresponds to the current month. If the date is not in the current month, it leaves the cell blank.

2. Copy the formula in C2 down to fill the cells for the entire month.

This modified approach ensures that the forecast is based only on the current month's data, even if you have data from earlier months in your dataset. It will stop at the end of the current month based on the system date.

[kevinedora]

@123abc I think this is for excel.

[123abc]

To create a simple forecast based on your logic, which is the average collection multiplied by the number of remaining days for the month, you can follow these steps in Power BI:

Assuming you have a table with columns "Date" and "Collection" like your example, and you want to calculate the "Forecast" column:

1. In Power Query Editor, make sure you have a table that includes all the dates up to the end of the month of your last collection date. You can create a date table with a series of dates using the "Enter Data" option.

2. Once you have your date table ready, create a new calculated column in your main table for the "Forecast." You can do this by selecting your main table, then go to the "Modeling" tab and click on "New Column."

3. In the formula bar for the new column, enter the following DAX formula to calculate the forecast based on your logic:

Forecast =
VAR LastCollectionDate = MAX('YourTable'[Date])
VAR RemainingDays = COUNTROWS(FILTER('DateTable', 'DateTable'[Date] >= LastCollectionDate))
VAR AverageCollection = AVERAGE('YourTable'[Collection])
RETURN
AverageCollection * RemainingDays

Make sure to replace 'YourTable' with the actual name of your main table.

1. After entering the formula, press Enter to create the new column. This column will now display the forecasted values based on your logic.

The DAX formula above calculates the forecast by first finding the last collection date and then counting the remaining days in the month. It then multiplies this by the average collection amount. The result is the forecasted value for each date, and it will be limited to the end of the month of the last collection date, as you requested.

Now, your table should have a "Forecast" column displaying the desired forecast values, just like in your example.

[kevinedora]

Hi @123abc,

Thanks for the quick response but I think this works only If I have September data only. However, I forgot to mention that what I have is from January 2023 collection data. I want to create a forecast based on the current month only as per my example. What your calculation did was include all the data I have for 2023.

Can we revise this? Thank you!

[123abc · Accepted solution]

I see what you mean. If you want the forecast to be based on the current month only, you can modify the DAX formula to consider only the data within the current month. Here's the revised DAX formula:

Forecast =
VAR CurrentMonth = MONTH(MAX('YourTableName'[Date]))
VAR CurrentYear = YEAR(MAX('YourTableName'[Date]))
VAR FirstDayOfMonth = DATE(CurrentYear, CurrentMonth, 1)
VAR LastDayOfMonth = EOMONTH(MAX('YourTableName'[Date]), 0)
VAR RemainingDays = IF('YourTableName'[Date] >= FirstDayOfMonth && 'YourTableName'[Date] <= LastDayOfMonth, LastDayOfMonth - 'YourTableName'[Date] + 1, 0)
RETURN
IF(ISBLANK('YourTableName'[Collection]), BLANK(), 'YourTableName'[Collection] * RemainingDays)

Again, replace 'YourTableName' with the actual name of your table.

This revised formula will calculate the forecast based on the current month and will only consider data within that month. It determines the first and last day of the current month and calculates the remaining days accordingly. If a date falls outside the current month, it will be excluded from the calculation.