cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamriz
Helper II
Helper II

How to dynamically show monthly cumulative record counts year on year for the past number of years?

Hi, I am trying to achieve something like the graph below. I already had the DAX function to get the cumulative counts monthly every year. But after many hours, I am still struggling to maintain the cumulative counts once I put it all out in one line graph similar to below. Also, I have data from year 2010, yet I only wanted to show cumulative data from year 2017 (or 3 years ago). How can I dynamically tell the graph to only display 3 years worth of data? I have here a sample PBIX file.  If anyone can help enlighten on this, I would really appreciate it. Thank you very much.

ques1.JPG

 

 

2 ACCEPTED SOLUTIONS
Mariusz
Super User II
Super User II

Hi @iamriz 

 

I've made some changes to your Model, Please see the attached 

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

View solution in original post

v-diye-msft
Community Support
Community Support

Hi @iamriz 

Kindly let me know if you'd like to get below one:

Measure = VAR currentDate = MAX('Table'[Date])

RETURN 
    CALCULATE(
        COUNTROWS('Table'),FILTER(ALL('Table'),[Date]<=currentDate),VALUES('Table 2'[Year]))

08.PNG

Pbix attached. 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

4 REPLIES 4
iamriz
Helper II
Helper II

Hi, @Mariusz, @v-diye-msft, thanks for being awesome, your answers helped me, thank you very much!!

 

HI @iamriz 

 

No problem.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

v-diye-msft
Community Support
Community Support

Hi @iamriz 

Kindly let me know if you'd like to get below one:

Measure = VAR currentDate = MAX('Table'[Date])

RETURN 
    CALCULATE(
        COUNTROWS('Table'),FILTER(ALL('Table'),[Date]<=currentDate),VALUES('Table 2'[Year]))

08.PNG

Pbix attached. 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

Mariusz
Super User II
Super User II

Hi @iamriz 

 

I've made some changes to your Model, Please see the attached 

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors