Forum Discussion

swanpaws's avatar
swanpaws
Frequent Visitor
2 years ago
Solved

Forecast value measure

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 h...
forecast
  • swanpaws's avatar
    swanpaws
    2 years ago

    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. 
swanpaws's avatar
swanpaws
Frequent Visitor
2 years ago

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.