How to calculate the current forecast count based on the rate and count in previous month

[PBI_newuser]

Hi,

I have a forecast rate Table to forecast the current month count based on last month.

For example, 2021-02 is 129 count and this month is 2021-03. The forecast count should be 129 * (1+10.5%) = 143.

How to create a measure to calculate if Month(Today()) = Rate[Month], then PreviousMonth(Date) * Rate?

Here is the sample.

[Anonymous · Accepted solution]

Hi @PBI_newuser ,

Please follow these steps:

1. Add a MonthNumber column to Rate table:

MonthNumber =
MONTH ( CONVERT ( [Month] & " 1", DATETIME ) )

2. Add a Rate column to Data table.

Rate =
VAR _month =
    IFERROR ( CONVERT ( RIGHT ( [Period], 2 ), INTEGER ), BLANK () )
VAR _m =
    IF ( _month = 12, 0, _month )
RETURN
    LOOKUPVALUE ( 'Rate'[Rate], 'Rate'[MonthNumber], _m + 1 )

3. Create measure:

Measure =
[ID Count]
    * ( MAX ( 'data'[Rate] ) + 1 )

The final output is shown below:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PBI_newuser]

Hi @Anonymous , thanks!!! It works. 😊

[amitchandak]

@PBI_newuser ,

Try with a date table

Forecast = CALCULATE([ID Count],DATESMTD(dateadd('Date'[Date],-1,MONTH))) * (1+CALCULATE([Rate],DATESMTD(dateadd('Date'[Date],-1,MONTH))))

or

Forecast = CALCULATE([ID Count],DATESMTD(dateadd('Date'[Date],-1,MONTH))) * (1+CALCULATE([Rate],DATESMTD(dateadd('Date'[Date],-1,MONTH)))/100)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[PBI_newuser]

Hi @amitchandak , how if the Date for Rate table is text?