Forum Discussion
Cumulative chart where the months dont align
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
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,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- Ritaf1983
Super User
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.
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,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi there - not sure how to upload the file but can you download from this link -
In the second tab if you hover over a month lets say February the actual date is 31st January
Thanks
