Forecast Recoveries

[swaggerboy69]

Hi Team,

I need help forecasting recoveries in Power BI.

So we have different systems in our organization. See example below:

The screenshot above are the Cost of the systems. Below are the dates when the systems go live:

Given those systems cost and the Systems go live date.

I want to be able to forecast recoveries.

So for the Month of December, I want to have a recovery of $816 and for January, I'm going to get $425.

Can you suggest a Query that would show using a Card visual the recovery for "Next Month" and also a table that would show recoveries every month?

Thank you!

[v-easonf-msft · Accepted solution]

Hi， @swaggerboy69 
Try codes as below:
1.Create a calendar table:
Calculated table:

Calendar = 
    ADDCOLUMNS(
        CALENDAR(DATE(2020,12,01),DATE(2021,1,31)),
        "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" )
    )

create a sort column for "YearMonthnumber":

No.Month = RANKX('Calendar','Calendar'[YearMonthnumber],,ASC,Dense) 

2. using lookupvalue  to  find the corresponding systems cost( calculated column)

Recovey = LOOKUPVALUE(Systems[Cost],Systems[System],Cost[Systems])

3.create measure to calculate next month recovery:

Next month Recovey = 
VAR _nextmonth =
    CALCULATE ( MAX ( 'Calendar'[No.Month] ), 'Calendar'[Date] = TODAY () ) + 1
RETURN
    CALCULATE ( SUMX ( Cost, Cost[Recovey] ), 'Calendar'[No.Month] = _nextmonth )

The result will show as below:

Please check attached pbix file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973]

Hi,

What are we missing here Team! where are those amounts 816 and 425 coming from? what is it that you call "Recovery"?

Can you elaborate more on this topic please!

[swaggerboy69]

Those are the monthly recoveries. Or total systems for December and January.

I just need a query that would compute the total of systems per month. Using a column date