Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Forecast Recoveries

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!

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi， Anonymous 
    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.

     

3 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    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!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

       

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi， Anonymous 
    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.