Forum Discussion

RGG's avatar
RGG
Frequent Visitor
7 years ago
Solved

Monthly Revenue Forecast Between Two Dates

Hi!  I am trying to build a monthly revenue forecast where I have a list of opportunities, the expected monthly value of each opportunity, the expected start date of the project, and the expected end...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi RGG ,

     

    Measure 3 = IF(ISINSCOPE('Table'[OPPORTUNITY]),CALCULATE(SUMX('CALENDAR',[Measure])),SUMX(ALL('Table'[OPPORTUNITY]),CALCULATE(SUMX('CALENDAR',[Measure])))
)

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi RGG ,

 

Here I created a sample for your reference, please check the following steps as below.

1. Create a calendar table as below and create relationship between tables.

 

CALENDAR = FILTER(CALENDARAUTO(),DAY([Date])=1)

2. To achieve our goal by a measure.

Measure = 
VAR std =
    CALCULATE (
        MAX ( 'Table'[START] ),
        ALLEXCEPT ( 'Table', 'Table'[OPPORTUNITY] )
    )
VAR endte =
    CALCULATE ( MAX ( 'Table'[END] ), ALLEXCEPT ( 'Table', 'Table'[OPPORTUNITY] ) )
VAR datecal =
    MAX ( 'CALENDAR'[Date] )
RETURN
    IF (
        std <= datecal
            && endte >= datecal,
        CALCULATE (
            SUM ( 'Table'[MONTHLY REVENUE] ),
            ALLEXCEPT ( 'Table', 'Table'[OPPORTUNITY] )
        ),
        BLANK ()
    )

 

Pbix as attached.

 

RGG's avatar
RGG
Frequent Visitor
7 years ago

This is fantastic.  Is there a reason why your matrix isn't summing the values by year?  For example, opportunity AAA in your example file should sum to $70K in 2019.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi RGG ,

     

    To create another measure based on [measure].

    Measure 2 = SUMX('CALENDAR',[Measure])

    • RGG's avatar
      RGG
      Frequent Visitor
      7 years ago

      Thank you for the update.  The solution works for each line, but the sum for each column is incorrect.  How do I get each column to also add up properly?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

        I do not know whom you are replying to.  Have you tried my solution?

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi RGG ,

       

      Measure 3 = IF(ISINSCOPE('Table'[OPPORTUNITY]),CALCULATE(SUMX('CALENDAR',[Measure])),SUMX(ALL('Table'[OPPORTUNITY]),CALCULATE(SUMX('CALENDAR',[Measure])))
)