Forum Discussion

kevinedora's avatar
kevinedora
Helper I
2 years ago
Solved

Forecast

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.   ...
Need Help
  • 123abc's avatar
    123abc
    2 years ago

    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.

123abc's avatar
123abc
Community Champion
2 years ago

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's avatar
kevinedora
Helper I
2 years ago

123abc I think this is for excel.