cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

julesdude_0-1638476798456.png

 

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?

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!