Running Total for custom cycles in line graph

[annamarie]

Hi All

I’m back with another issue I just cannot get right. I need to display running total sales figures for a few cycles on one line graph, with current partial cycle, to see how it tracks against previous cycles. I’ve watched some of Sam’s videos and put some measures in. I need this to be dynamic for the last 6 cycles (or more). This is an example of what the graph should look like (just nicer).

But my effort returns this.

I think I’m misunderstanding which dates to use, cycle date vs date vs day of the month. The cycles run from 5 of each month to the 4th of each month. Noting of course that some months only have 30 days, but with a running total should react correctly.
I’ve attached the pbix, the excel data, on the excel is the example of what I’m trying to achieve on sheet 2.
I’m wondering if there is a way for the legend to show the cycle date reflecting in the graph, but in my experience, I don’t think that it’s possible.
The axis does not need to say Day 5 etc, but just 5 is correct, I just added it in order for sorting to be correct.
I have not marked the date table as a date table, I am aware, the reason is that in my actual report I have 2 date tables as I report on different countries with different cycle dates, so I’m not sure of the implication should I mark 1 as a date table.
Please ask any questions. Hoping my issue is clear enough. I saw other posts similar, but couldn’t quite get it aligned to mine.
Thanx
Annamarie
Sales example.pbix (117.5 KB) Sales Example.xlsx (71.0 KB)

[Harsh]

Hello @annamarie,

Thank you for posting your query onto the Forum.

In order to achieve this type of result (close enough). You’ll be required to create two dimension tables that connects with the date tables. Below are the screenshots provided for the reference -

Since you want “Day Number” into the X-axis. Create a table “Day Number” wise alongwith the sorting order -

Now, since you want the customized month cycle. That is, for month that starts from 5th Jul you want it to be termed as next month i.e. “4 Aug cycle”. Create a “Month Wise Cycle” table.

Now, create a relationship of these two tables with the date table. Below is the screenshot provided for the reference -

And lastly, you can have the chart that you’re trying to achieve (as mentioned earlier - “close enough”). Below is the screenshot of the results provided for the reference -

Important Notes:-

1. There are certain months with the end dates either 28 , 29 (in case of leap year) or 30. So due to this the total of Day 28, Day 29 and Day 30 cannot be carry - forwarded to the Day 31 and that’s why graph breaks in between.

2. The reason why in your case it’s showing a continuous graph is because firstly you’ve inserted the figures manually for Day 31 wherever the months are not in terms of 31 days and second thing is you’ve also created the month cycles column manually. Below is the screenshot provided of your data alongwith the cells highlighted in yellow by you-

3. In order to cross - check the numbers whether the figures are carry - forwarded or not. I analyzed the numbers into the table as well. Below is the screenshot provided for the reference -

4. Last Resort, so now, if you want to create a chart that has continuous numbers, you’ll be required to prepare the tables for each cycles manually like you’ve done in Excel since with the combination of dates to achieve this results is not possible. Since date table will restrict you from achieving this type of results.

I’m also attaching the working of the PBIX file for the reference.

Conclusion: Had the start date and end date of the cycle been normal than the results would’ve automatically fallen in place.

Thanks and Warm Regards,
Harsh

Running Total - Harsh.pbix (123.9 KB)

[EnterpriseDNA]

Hi @annamarie, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[EnterpriseDNA]

Hi @annamarie, we’ve noticed that no response has been received from you since the 30th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!