Sum Current Month + 2 AND Next Month + 1 and Month After that

Question

I have a metric I need to replicate in DAX for PowerBI and I am not quite sure how to do it.

The basics are this:

Fact Table: Opportunity

Dimensions: Created Date, Closed Date

The metric goes like this, I will just give an example, because I really dont know how to explain it.

SUM OF:

Created in FEB and Closed Date in FEB, MAR, APR

Created in MAR and Closed Date in MAR, APR

Created in APR and Closed in APR

This would happen for each month in the table/matrix.

Seems like I would need some variables something like

Measure =

VAR Month1 = SUM(ClosedOpps) where ClosedDate between CurrentMonth and CurrentMonth + 2

VAR Month2 = SUM(CLosedOpps) where ClosedDate betwwen CurrentMonth + 1 and CurrentMonth + 2

VAR Month3 = SUM(ClosedOpps) where ClosedDate = CurrentMonth + 2

Return Month1 + Month2 + Month3

My understanding is, the Closed Date filter would be the Table/Matrix Visual when I drag the column MonthYear into the visual

EDIT:

Here is a simplified replica of what they are doing in Excel

So The data on the left is the fact table. You can see when the Opps are created, when they are closed. I added in the Created MonthYear and the Closed MonthYear. The Pivot is what they have now in Excel. Dates across the top (Columns) are Created YearMonth, Dates for the Rows are Closed YearMonth.

I need to be able to SUM the numbers inside the of I3:K5 which total 5500 in the example.

UPDATE:

So I have added in a suggested Date Dimension table, Duplicated it (One for Open Date, one for Closed Date) I added a column DateDIM_KEY to each which is just a numerical index. The fact table has these keys, and they are loaded off of the same date range (2013 to 2030). The column ActualValue in the fact table is the column we would SUM.

Here is the updated Fact table sample. I pulled the DateDIM_KEY values directly from the date dimension for those dates.

greggyb greggyb · Accepted Answer · 2019-08-23T17:07:25

You need a good date dimension. And you need to have it roleplaying for OpenDate and CloseDate. There are many good date dimensions out there. I like mine.

Assuming that you're putting 'OpenDate'[Month] on an axis label.

Opportunity Value = SUM ( 'Opportunity'[Value] )

MyMeasure iterator =
// start of the month on the current row of a pivot/axis label of a chart
VAR CurrentMonthStart = MIN ( 'OpenDate'[Date] )
// End of the month 2 months out
VAR ThreeMonthsOutEnd = EOMONTH ( CurrentMonthStart, 2 )
// This represents one row per month. You could also use a MonthAndYear type field.
// We will walk through the three open months we care about, and in each will sum
// the value for the opportunities opened in that month, with additional filters.
VAR NextThreeOpenMonths =
  CALCULATETABLE (
    VALUES ( 'OpenDate'[MonthIndex] ),
    ALL ( 'OpenDate' ),
    DATESBETWEEN ( 'OpenDate'[Date], CurrentMonthStart, ThreeMonthsOutEnd )
  )
RETURN
  // Iterate each OpenMonth
  SUMX (
    NextThreeOpenMonths,
    // On each step of the iteration, grab the start of the currently iterated month
    VAR IterMonthStart = CALCULATE ( MIN ( 'OpenDate'[Date] ) )
    RETURN
      CALCULATE (
        [Opportunity Value],
        // There is date context from visuals we want to ignore:
        ALLEXCEPT ( 'OpenDate', 'OpenDate'[MonthIndex] ),
        // filter CloseDate to be between the start of the currently iterated
        // open month and the originally calculated ThreeMonthsOutEnd. The latter
        // is static within the scope of the iteration.
        DATESBETWEEN ( 'CloseDate'[Date], IterMonthStart, ThreeMonthsOutEnd )
      )
  )

Also, while writing the previous iterative approach, I realized we could do the work in a single setfilter:

MyMeasure set =
// MonthIndex is in my date dimension - super useful for arithmetic on dates.
// Read the readme.
VAR C = SELECTEDVALUE ( 'OpenDate'[MonthIndex] ) // want a short name below
// Table literal syntax - two column table, where each parenthesized expression
// forms a row. If it were much more, I'd do something clever with generate, but
// six cases are easy to write by hand.
VAR MonthFilters = {
  (C, C),
  (C, C+1),
  (C, C+2),
  (C+1, C+1),
  (C+1, C+2),
  (C+2, C+2)
}
RETURN
  CALCULATE (
    [Opportunity Value],
    TREATAS ( MonthFilters, 'OpenDate'[MonthIndex], 'CloseDate'[MonthIndex] )
  )

I like the latter a lot better, but didn't think of it until after writing the iterative version, so I'm leaving both. Set-based should be better performing.

Edit: some screengrabs I forgot:

Here's the relationship diagram for roleplaying date dim:

And here's the visual in action with both measures:

Sum Current Month + 2 AND Next Month + 1 and Month After that

2 Answers