Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DMatus
Frequent Visitor

Cumulative sum of a fixed, monthly value?

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 :(?

 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@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]))

96.PNG

 

 

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

 

Regards,

 

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@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]))

96.PNG

 

 

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

 

Regards,

 

Thank you very much for your answer! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.