Cumulative sum of a fixed, monthly value?

[DMatus]

This has been troubling me for the entire day and I'd really appreciate some help on the matter.

I have the following formula: 

CountCallsYTD = CALCULATE(COUNT(Actions[ID_Action], DATESYTD(Calendar[ID_DateText])))

Which works just fine and produces the following chart:

http://imgur.com/a/8rbAN

Now what I'd like to add to this is a "target" cumulative number of Actions per month, so for example, if on average we need to make 30 calls a month, January would show 30, February would show 60 and so on until December (which would show 360).

I had to do something similar with daily targets but that was way easier, I've tried many ways to do this but it seems I just lack the knowledge necessary for such a simple thing, could I get some help on this :(?

[v-sihou-msft · Accepted solution]

@DMatus

In this scenario, since your the granularity in your fact table is day level, it's better to assign the target value on day level as well. I assume you have a full calcendar table. Firstly, you need to add a "YearMonth" column in this table for grouping.

YearMonth = YEAR('Table'[Date]) & "" & MONTH('Table'[Date])

Then just add a column for Daily Target.

Daily Target = 30/ CALCULATE(COUNTA('Table'[Date]),ALLEXCEPT('Table','Table'[YearMonth]))

Now you should be able to cumulative sum with a fixed 30 on month level.

Regards,

[DMatus]

Thank you very much for your answer!