Forecast value measure

[swanpaws]

I'm trying to generate a measure which will track the amount of approved jobs out of all jobs forecasted for the current month.

The problem

In the guage visual above I currently have approved documents out of max approved (monthly) historically. This is because I have to look back in time for the denominator (i.e. max value) because jobs are generated on an ongoing basis. So I can't know at the beginning of each month how many jobs will be created. And the max value only gives an approximation of how many jobs will be created and is therefore not very accruate.

The issue with using max is that it's backward-looking and as is clear from the historical time series, the # of jobs each month are growing. What I really would like is a measure which gives an estimate value of jobs to be expected for the current month which is forecasted from the count of jobs historcially approved each month.

The solution would be a measure version of the forecasting available in analytics which will give a numerical value of jobs forecasted to be generated this month.

Is there a way to generate this forecast with DAX code in Powerbi?

Thanks in advance!

[swanpaws · Accepted solution]

Solved in the end with the following:

CurrentMonthForecast =

VAR PreviousMonth = EOMONTH(TODAY(), -1)

VAR FirstMonthStatusCount = 64

VAR LastMonthStatusCount = [StatusCountPreviousMonth]

VAR NumberOfMonths = DATEDIFF(DATE(2023, 7, 31), PreviousMonth, MONTH)

VAR GrowthRate = ( LastMonthStatusCount / FirstMonthStatusCount) ^ (1 / NumberOfMonths) - 1

RETURN LastMonthStatusCount * (1 + GrowthRate)

StatusCountPreviousMonth was a count measure of a column which checked for previous month and returned status.