Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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

 

Basically, I have the following table:

 

idvalueyearAsOfDate
145.34202031 December 2020
244.10202115 March 2021
310.12202118 April 2021
49.12202010 December 2020
58.15201906 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?

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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