Cumulative Total Line Graph Showing Legends by Year & Daily Granularity but X Axis Labelled by Month

[Anonymous]

HI there,

Following previous help, I have a line graph as below which i have created to look like this:
Axis:
Date:
   Month

   Day

Legend:

Date:

   Year

Values:

cumulative

DAX Measure:

cumulative = CALCULATE(SUM(DATA[PnL]),FILTER(ALL(DATA),DATA[Date]<=MAX(DATA[Date])),VALUES(DATA[Year]))

My DATA table is the only table in the model. I actually don't have a calendar table but might add it if I really must. For the moment I've kept things simple and created additional Custom Columns in Power Query that reference the Date column. The DATA table looks a little something like this:

Date	PnL	Year	Month Number	Month Name	DayOfYear	Index
01/01/2022	10	2022	1	January	1	1
02/02/2022	-5	2022	2	February	2	2
20/06/2021	64	2021	6	June	171	3
27/06/2021	83	2021	6	June	178	4
28/06/2021	21	2021	6	June	179	5
29/06/2021	-10	2021	6	June	180	6
02/04/2020	-43	2020	4	April	92	7
03/04/2020	48	2020	4	April	93	8
04/04/2020	96	2020	4	April	94	9
05/04/2020	23	2020	4	April	95	10

My requirement is that whilst the graph above is ok, I have daily granular detail in my table, pretty much for each day going back a number of years, and current graph resolution is showing monthly granularity. I would like daily granularity. If i drill down a level using the drill-down icon, it groups the PnL into day groups from 1 -31 to correspond with each day of each month, which doesn't work.

I know I could just use the DayOfYear attribute from the table as an index reference, but that would then give me labels on my x axis of 1 - 365. I would kind of still like the labels to remain showing monthly categories so the viewer can see monthly index points.

How do I achieve this?

Finally, you'll notice that the current year's PnL flatlines across from last date available to the end of the axis. I would want this to be blank for future dates of the year. I think this is achieved by a simple IF statement in the DAX which detects current date, but how do I express this?

[diego_ba01]

were you able to figure it out? i need help with the same thing. please let me know. thank you so much!

[Anonymous]

Hmmm, still trying for a solution on this one but no luck. I would like the x axis to display month labels rather than day of the year labels, so granularity can be by day, and secondly for the current year I need the line to stop at the current date rather than continue to the end of the graph.
Can anyone help?

[Anonymous]

Hi @v-yiruan-msft 

Thanks for getting back to me. This looks good, however what I need is for the lines to be stacked together on a timeline spanning a year rather than run across a continual timeline.

Can it be tweaked?

[Anonymous]

Can anyone provide help? I can add a calendar table if needs be.

[v-yiruan-msft]

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below to get YearMonth

YearMonth = YEAR ( 'DATA'[Date] ) & FORMAT ( 'DATA'[Date], "MMMM" )

2. Create a line chart with below settings

Best Regards

[Anonymous]

Thanks for getting back to me @v-yiruan-msft . Sorry I think I replied in the wrong place. This looks good, but not quite there - the lines need to be grouped not spread like that, like in my first graph. I would like the x axis to display month labels rather than day of the year labels, so granularity can be by day, and secondly for the current year I need the line to stop at the current date rather than continue to the end of the graph.

Can it be tweaked?