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.

[v-cazheng-msft · Accepted solution]

Hi, @PBI_newuser 

You can try the following steps.

1 Create a Calculated column

Month = FORMAT(data[Date],"mmm")

2 Create a Measure

Forecast_Count =

VAR val =

    CALCULATE ( COUNT( data[ID]),ALLEXCEPT(data,data[Date]), DATEADD(data[Date],-1,MONTH))

VAR rat =

    CALCULATE (

        MAX ( 'Rate'[Rate] ),

        FILTER ( 'Rate', 'Rate'[Month] = MAX ( data[Month] ) )

    )

RETURN

   val*(1+rat)

The result looks like this:

Here is the pbix.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[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.