Cumulative chart where the months dont align

[Anonymous]

Hi there

In my dataset Ive got two columns are the original columns Year and Period ( e.g 1 or 2 ....to 12), from this Ive created a "Date" column. The code says that if the Year is 2024 and the Period is 0 then the date is 1/1/24. So this Date columns contains all code driven dates.

#"Added Conditional Column" = Table.AddColumn(#"Removed Errors", "Month", each if [Period] = 0 then #date(2024, 1, 1) else if [Period] = 1 then #date(2024, 1, 31) else if [Period] = 2 then #date(2024, 2, 29) else if [Period] = 3 then #date(2024, 3, 31) else if [Period] = 4 then #date(2024, 4, 30) else if [Period] = 5 then #date(2024, 5, 31) else if [Period] = 6 then #date(2024, 6, 30) else if [Period] = 7 then #date(2024, 7, 31) else if [Period] = 8 then #date(2024, 8, 31) else if [Period] = 9 then #date(2024, 9, 30) else if [Period] = 10 then #date(2024, 10, 31) else if [Period] = 11 then #date(2024, 11, 30) else if [Period] = 12 then #date(2024, 12, 29) else if [Period] = 90 then #date(2024, 12, 30) else if [Period] = 91 then #date(2024, 12, 31) else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Month", type date}}),

My struggle is when I want to create a cumulative line graph, because when I do this the months on the x axis dont correspond to the points on the line graph. Please see the visual below and the table to the right of it are the values.

Thanks in advance

[Anonymous]

Hi there - not sure how to upload the file but can you download from this link - 

Test.pbix

In the second tab if you hover over a month lets say February the actual date is 31st January

Thanks

[Anonymous]

Thanks for the concern from Ritaf1983.

Hi @Anonymous ,

Noticed you didn't follow up with a reply, has your issue been resolved yet, I couldn't reproduce your issue during my testing process :

Measure = CALCULATE([Profitmeasure],FILTER(ALL('financials'),'financials'[Date]<=MAX('financials'[Date])))

If your problem is not solved, please provide a more detailed description or a pbix file with sensitive data removed, this will help the problem to be solved faster!

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Anonymous]

Hi Zhu - have replied to this thread. Im quite computer savvy but couldnt find the option to edit the original post.lol

[Anonymous · Accepted solution]

Hi @Anonymous ,

I'm sorry for misunderstanding you in my last reply, thanks for the file, you can set it up as follows:

1.Change the format of month to "mmmm yyyy", or you can recreate the same column.

2. Under the visualization pane, change the type of x-axis from continuous to categorical.

3. Create a measure that displays the date and place it in the tooltip.

Date = MAX('TEE01ExtractFileWithHeaders'[Month])

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Ritaf1983]

Hi @Anonymous 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216