Line chart showing Running total by Financial year and month

[joebax86]

Hi,

I have looked at lots of posts about this and feel close but can't quite get how I want, my end goal is to get something that looks like this;

I have lots of dates, with duplicates and have worked out a running total that 'resets' at a new FY start. Below is an example of how this looks;

Date	Month	Financial Year	RT
1/5/21	May	2022	4
1/7/21	July	2022	7
1/8/21	August	2022	10
1/11/21	November	2022	13
1/5/22	May	2023	3
1/7/22	July	2023	9
1/8/22	August	2023	17
1/11/22	November	2023	18
1/5/23	May	2024	2
1/7/23	July	2024	6
1/8/23	August	2024	16

Running total is calculated as;

RT       = CALCULATE(

                   COUNT('Table'[ID]),

                   DATESYTD('Table'[Date], "04-30")

But the ouput im getting currently is as below (instead of a running total its just count by month);

My assumption its something to do with the month field and how it interacts with the running total. When I replace this field on the x-axis with the date field I get the below, this is closer but as shown at the top I only want each month once.

Thanks in advance

[parry2k · Accepted solution]

@joebax86 You are almost there, as a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist