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

[iamriz]

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.

[Mariusz · Accepted solution]

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

[iamriz]

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

[Mariusz]

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 · Accepted solution]

Hi @iamriz

Please let me know if you would like to get below one:

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

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

Pbix attached.

[Mariusz · Accepted solution]

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