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

Accumulative Sum Issues over multiple years with set 'Month' Axis

First one here so here goes, I hope I did the majority of Do's and hopefully no Don'ts... Been struggling for weeks in between millions of other jobs but I've cracked.

 

Trying to create an accumulative line chart using the same axis, that being April to (ideally Oct the following year but that's another story...) March. 

 

The accumulation measure is fine if I have [enrol_date] on the axis (Left Image) but breaks when I change to Month (from the date table) (Right Image) as in the visual above (see the two following photos).

 

ryancooper327_0-1632410240259.png   ryancooper327_1-1632410255924.png

 

ryancooper327_2-1632410318103.png

 

Regarding the accum. measure I have sum'd column rn in measure 0Measure (don't hate me) and then

 

00Measure =
CALCULATE([0Measure],
FILTER(ALLSELECTED(Query1[ENROL_DATE]), Query1[ENROL_DATE] <= MAX(Query1[ENROL_DATE])))

 

Sample Data Link:

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@ryancooper327 , fi you are using axis from date table then try like

 

00Measure =
CALCULATE([0Measure],
FILTER(ALLSELECTED(Date), Date[Date] <= MAX(Date[Date])))

 

or

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

@ryancooper327 first, if you are working with dates, you should always add a calendar dimension in your model (part of Do's), you can following my blog post here to add one Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

 

for now try this measure, otherwise use calendar dimension and use that in your measure:

 

00Measure =
CALCULATE([0Measure],
FILTER(ALLSELECTED(Query1), Query1[ENROL_DATE] <= MAX(Query1[ENROL_DATE])))

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

amitchandak
Super User
Super User

@ryancooper327 , fi you are using axis from date table then try like

 

00Measure =
CALCULATE([0Measure],
FILTER(ALLSELECTED(Date), Date[Date] <= MAX(Date[Date])))

 

or

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

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.