Stacking Accumulated Values, Legend Split by Year in a Line Graph

[Anonymous]

Hi - slightly tearing myself apart over this one - feels like i'm so close.

Basically, I have the following table:

id	value	year	AsOfDate
1	45.34	2020	31 December 2020
2	44.10	2021	15 March 2021
3	10.12	2021	18 April 2021
4	9.12	2020	10 December 2020
5	8.15	2019	06 June 2019

For my line chart:

Axes

AsOfDate

Legend
Year

Value
PnL

PnL is the following measure:

PnL = calculate(sum(DailyPnl[Value]), DATESYTD(DailyPnl[AsOfDate],"31/12"))

The graph comes out like the following

However, no matter how many combinations I've tried, I cannot get these bound together rather than a rolling timeline - I just want a year window with each year legend spanned across it and sort of overlapping.

What am I missing?

[Ashish_Mathur]

Hi,

I am not sure of what sort of a graph do you want but this is the approach you should follow:

1. Create a Calendar Table with Year as a calculated column
2. Build a relationship from the AsofDate column of the DailyPnL Table to the Date column of the Calendar Table
3. To your visual, drag Year from the Calendar Table
4. Write these measures
   1. Total = SUM(DailyPnL[Value])
   2. Total YTD = calculate([Total],datesytd(calendar[Date],"31/12"))

Hope this helps.

[Anonymous]

Thanks Ashish. Unfortunately it didn't work. I get the same chart as above. I put the Date from my date table into the Axis as you said, the Year from the date table conditional column I had into the Legend and the PnL measure into the Values.
Just to clarify, each year should span across a year window period of time in the chart. So they should all be clumped together, and being accumulative, all be rising upwards together agains each month

[Ashish_Mathur]

Share the link from where i can download your PBI file.

[amitchandak]

@Anonymous , based on what I got, If need overlapping you need measures not legend and you will not get year wise label. Also Always use date table for time intelligence

PnL = calculate(sum(DailyPnl[Value]), DATESYTD(Date[Date],"31/12"))

PnL LY= calculate(sum(DailyPnl[Value]), DATESYTD(dateadd(Date[Date],-1,year),"31/12"))

PnL LLY= calculate(sum(DailyPnl[Value]), DATESYTD(dateadd(Date[Date],-2,year),"31/12"))

PnL L2LY= calculate(sum(DailyPnl[Value]), DATESYTD(dateadd(Date[Date],-3,year),"31/12"))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.