Forum Discussion
Forecast
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.
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:
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.
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."
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.
- 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.
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!
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.