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.

kevinedora's avatar
kevinedora
Helper I
2 years ago

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's avatar
123abc
Community Champion
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.