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
muelleto
Regular Visitor

Accumulate Monthly Values

Hi,

 

after hours of research and workarounds I'm still struggeling with a huge problem.

I have a list of demands with the column "close date", the column "service" and the column "efforts". Now I'd like to have a stacked column chart with a y-axis displaying the effort and x-axis dsplaying the months from January to the actual month and the stacks should represent the service.

In addition the values from the previous month should be added to the next month.Capture.PNG

 

In the end it should look like this with increasing values but I'm not able to addup the effort from the previous month. 

 

 

 

 

Any ideas how to solve this?

Thanks you!

Br, Tom

1 ACCEPTED SOLUTION
muelleto
Regular Visitor

Ok fixed the problem.

I forgot to add .[Date]

 

So the solution looks like this:

 

 

EffortYTD = TOTALYTD(SUM(Table[Effort)]); Table[Closed Date].[Date])

 

Br, Tom

View solution in original post

5 REPLIES 5
muelleto
Regular Visitor

Ok fixed the problem.

I forgot to add .[Date]

 

So the solution looks like this:

 

 

EffortYTD = TOTALYTD(SUM(Table[Effort)]); Table[Closed Date].[Date])

 

Br, Tom

igor
Frequent Visitor

Ive try this solution and it works, but I dont know how to use in two distinct years.

Can you help me?

powerbi.JPG



Vvelarde
Community Champion
Community Champion

@igor

 

hi, in this case you need a cumulative Total

 

Check this: http://www.daxpatterns.com/cumulative-total/

 

 




Lima - Peru
muelleto
Regular Visitor

@Vvelarde Thank you for your input.

 

Now I get the error: Calculation error in measure 'Report'[EffortYTD]: A date column containing duplicate dates was specified in call to function 'DATESYTD'. This is not supported.

 

I know what that means but no idea how to fix this...

 

Anyhow I think, this is not going to solve my problem because I use the 'closed date' in the Axis field to split the Sum('effort) values into the different month. So it will always just show me the values for each month and it will not add them up. Correct?

 

Br, Tom

Vvelarde
Community Champion
Community Champion

@muelleto

 

Try with this:

 

EffortYTD = TOTALYTD(SUM(Table3[Effort]),CalendarTable[Date])




Lima - Peru

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.