Forum Discussion
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).
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:
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.
2 Replies
- amitchandak
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.
- parry2k
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 Solutions
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.⚡
