YTD measure calculated separately for each month

Question

I have a measure calculated in the context of a selected month defined as:

MyMetric = COUNTROWS ( FILTER ( Entities, [Incident Count] > [Target] ) )

I need to calculate the YTD number while calculating each month separately. This is because a single Entity that exceeds the Target in two months needs to be counted twice, whereas a simple YTD calculation would only include it once. For example, when reporting March, a correct result is achieved with:

[MyMetric YTD] = [MyMetric]
                 + CALCULATE ([MyMetric] , DATEADD(DateTable[Date], -1 , MONTH))
                 + CALCULATE ([MyMetric] , DATEADD(DateTable[Date], -2 , MONTH))

Obviously, this is not the right way to do it. How can this kind of calculation be written efficiently?

Alexis Olson Alexis Olson · Accepted Answer · 2018-09-04T19:53:11

Let's suppose that you have a Month column in your DateTable. If not, then you can create one.

Then you can try something along these lines:

MyMetric YTD
= SUMX(
      CALCULATETABLE(
          VALUES( DateTable[Month] ),
          DATESYTD( DateTable[Date] )
      ),
      [MyMetric]
  )

Basically, you get a list of each month in the year so far and then sum your measure value for each one of those months.

YTD measure calculated separately for each month

1 Answers