Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MilindG
Frequent Visitor

Need help with YOY line chart

Hello Community!

 

I'm very new to the Power BI and I need your help creating a year over yer comparision line chart.

At the moment I have created a line chart however for the PY it shows full year whereas I only want it to display same yearweek as current.

 

Another thing I need help with is creating a calculation field  'Change' which calculates the difference beween CY sales vs LY sales YTD (LY should only go upto max this years week. ). My data shows weekly and end of last week. I want the YTD total to display CY, and LY should be end of prior week LY(08/13/2022)

 

Below is how somewhat my data displays

MilindG_0-1692040580234.png

Id really appreciate any help. Thank you!

1 ACCEPTED SOLUTION

@MilindG 

you can create a scope column, then only do the calculation when scope is y

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
MilindG
Frequent Visitor

@ryan_mayu  Thank you again. This solved my problem.

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




MilindG
Frequent Visitor

@ryan_mayu  Sorry for the late reply. This is exactly the format I was looking for. I need one additional thing changed and need your help with. My current data is looking YTD and ends last Sat. So for the current report, it should have data from 1/1/23 - 9/2/23. I want the PY to be compare the same time period(Week) so itd be 1/2/22 - 9/3/22 if that makes sense.

@MilindG 

you can create a scope column, then only do the calculation when scope is y

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu Hello I have one more question. Similar to here where you helped me get yoy data for same date time. How would the forumla change if I'm bringing one more year(2021) I'm looking to compare same timeframe for current year, Previous year and PPY.

 

'

scope =
VAR _year=year('Table'[Week End])
VAR _py=EDATE(TODAY(),-12)
RETURN IF(_year=YEAR(TODAY()) || _year=year(today())-1 && 'Table'[Week End]<=_py,"y")'

I'm struggling to add the forumla for the PPY

 

@MilindG 

maybe you can try this

scope =
VAR _year=year('Table'[Week End])
VAR _py=EDATE(TODAY(),-12)
VAR _ppy=EDATE(TODAY(),-24)
RETURN IF(_year=YEAR(TODAY()) || _year=year(today())-1 && 'Table'[Week End]<=_py||_year=year(today())-2&&'Table'[Week End]<=_ppy,"y")




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu Excellent!! This is exactly what I was looking to do. Thanks a lot for all your help!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu  Sorry to bother you again, I have one more question.

MilindG_0-1694192818958.png

Mine doesnt show up in this order. it show up Change, PY, CY, Change% or like below

MilindG_1-1694192882838.png



I tried dragging it or rearranging but its not working. How do I get these in the order like yours?

Also how do I get rid of the decimals and round up?



i have another order table which set up the orders. you can check the attachment i provided.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




MilindG
Frequent Visitor

@ryan_mayu 

SiteYearWeek EndYearWeekNameAmountCY vs PY
120225/7/202218AAA500PY
220235/6/202318BBB100CY
320225/7/202218CCC300PY
320225/7/202218EEE350PY
320225/7/202218AAA100PY
220235/6/202318BBB500CY
120225/7/202218BBB750PY
120235/6/202318AAA600CY
220225/7/202218AAA150PY
120235/6/202318EEE35CY
320225/7/202218AAA95PY
120225/14/202219EEE160PY
320235/13/202319AAA285CY
220225/14/202219EEE125PY
220235/13/202319EEE150CY
220225/14/202219CCC200PY
120235/13/202319EEE450CY
120225/14/202219CCC365PY
120225/14/202219AAA250PY
220235/13/202319BBB950CY
220225/14/202219BBB1000PY
320235/6/202318CCC350CY
220235/13/202319CCC625CY

 

Heres my sample data. I already have this dashboard on Tableau and I'm trying to recreate it on PowerBI. Below is a snip of what I'm trying to get

MilindG_0-1692107927687.png

At the moment I have the line chart created however I'm unsure on how to create the red boxed section which displays current year total(YTD), PY total(YTD total as same yearweek as CY), Change in $ and change%.

@MilindG 

here is a wordaround for you

you just need to change the name filter to create the box for other names.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

could you pls paste the sample data (not the screenshot) and also provide the expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors