Calculate result for next 4, next 10 and next 16 weeks based on month from matrix row

[Anonymous]

Based on the first column of the matrix (year and month hierarchy), i would like to create a measure that perform a calculation based on another measure.

The second column "Calculation" is fine as it receives the date input from the date hierarchy. However, i struggle to perform the calculation for "Next 4" weeks (and also for "Next 10", "Next 16", etc.)

The calculation "Next 4" is supposed to take the first date of the month, return the following 4 weeks (or 28 days), and evaluate another measure for the set of dates returned. In example, for january 2020, I want it to perform the calculation for the range between January 1st and January 28th.

Similarly, the calculation "Next 10" is supposed to take the first date of the month, return the following 10 weeks (or 70 days), and evaluate another measure for the set of dates returned. In example, for january 2020, I want it to perform the calculation for the range between January 1st and March 11th.

"Estimated revenue" is calculated in another measure, which works fine for the monthly date ranges provided by the table.
 

Planned next 4 weeks (dynamic) = 
// Calculating estimated revenue for the next 28 days from the start of a given month. 
CALCULATE(
    [Estimated revenue]; 
    DATESINPERIOD(
        Revenue[Date]; 
        STARTOFMONTH(DateTable[Date]);
        28;
        DAY
    )
)

[daxer-almighty · Accepted solution]

@Anonymous 

Here's the code you need

// This measure works for any period of
// time selected, not only for months.
// Bear in mind that Dates must be a date table in
// the model marked as such for the time-intel
// functions to work correctly.
[Next N-Weeks Calculation] =
// I hard-code the number of weeks but
// you can easily harvest this value
// from a disconnected parameter table to
// make this calculation fully dynamic.
var NWeeks = 10 // could be 4, 5, 6, 7, 8...
var NumberOfDays = NWeeks * 7
// First, get the first date in the
// current context. If a month is visible,
// you'll get the first day of the month,
// of course, so this definition tallies
// with your original need.
var FirstVisibleDate = MIN( Dates[Date] )
// This period starts with the first
// day in the current context and returns
// all the dates that cover NWeeks.
var PeriodToCalcOver =
    DATESINPERIOD(
        Dates[Date],
        FirstVisibleDate,
        NumberOfDays,
        DAY
    )
var NumberOfDaysCovered =
    COUNTROWS(
        PeriodToCalcOver
    )
var Result = 
// To return a result, you have to have
// enough days in PeriodToCalcOver. This
// might not be the case if you are too
// close to the right edge of the calendar.
// Then you want to return BLANK.
    IF( NumberOfDaysCovered = NumberOfDays,
        CALCULATE(
            [Estimated Revenue],
            PeriodToCalcOver
        )
    )
return
    Result

[Anonymous]

@daxer-almighty - Thank you very much for the code and great explanation, this is perfect!

To my understanding it was the part related to "Bear in mind that Dates must be a date table in the model marked as such for the time-intel functions to work correctly" that caused the issue. With this corrected the original measures also worked fine, however being far less fool-proof than yours...

One follow-up question related to "I hard-code the number of weeks but you can easily harvest this value from a disconnected parameter table to make this calculation fully dynamic.": How would you suggest to go forward to make the number of weeks dynamic? 

[daxer-almighty]

"How would you suggest to go forward to make the number of weeks dynamic?"

Well, exactly as I explained in the code. Create a parameter table with all the numbers of weeks you need and then harvest the value from the table using SELECTEDVALUE( 'Number Of Weeks'[NWeeks] ) and use the value instead of the hard-coded one.

[Greg_Deckler]

@Anonymous - What is the formula for [Estimated revenue]? What is going wrong with your current calculation?

[Anonymous]

@Greg_Deckler - I realise that [Estimated revenue] was named wrong. For consistency I'll stick with it even though it's impresice, as I hope it will be understandable from the measure below:

[Estimated revenue (actually: % of manhours capacity sold to customers)] = 
   DIVIDE(
      CALCULATE(
         SUM( Revenue[Planned hours] );
         Revenue[Type] = "Billable"
      );
      SUM( Capacity[Manhours avaiable] )
   )

[Greg_Deckler]

@Anonymous So what happens if you do something like this?

Planned next 4 weeks (dynamic) = 
VAR __Date = MAX(DateTable[Date])
VAR __Month = MONTH(__Date)
VAR __Year = YEAR(__Date)
VAR __MinDate = DATE(__Year,__Month,1)
VAR __MaxDate = __MinDate + 28
// Calculating estimated revenue for the next 28 days from the start of a given month. 
RETURN
CALCULATE(
    [Estimated revenue];
    FILTER('Revenue',[Date]>=__MinDate && [Date] <=__MaxDate)
)

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

[Anonymous]

@Greg_Deckler - I get the same result as I do with the previously posted measures. I have discovered that it works fine for "Next 4", but not "Next 10" and "Next 16". I believe the issue is related to the dates being filtered by the first column of the matrix, meaning that for January 2020, only the first 31 days are used for the calculation (whereas I want it to use the full 70 days in the date range for "Next 10" even though this continues into February and March).

Thank you for posting the two links - I'll have a look!