Forum Discussion
Comparing previous and current year data on a line chart
Hi priyapalanki,
if you have 2 separate tables FY16 and FY17, it's easy now.
- Create Dates table for time pattern
Dates = CALENDARAUTO()
- Create Prev-Date Column
Prev - Date = DATEADD( Dates[Date],-1,YEAR)
- Union 2 separate tables FY16 -FY17 into 1 table:
u = UNION(FY16,FY17)
- Create 2 calculated measure to cumpute total amount of FY16 and FY17:
FY16 = CALCULATE(SUM(u[Amount]),FILTER(all(dates),Dates[Date]=MAX(Dates[Prev - Date]) ))
FY17 = CALCULATE(SUM(u[Amount]),FILTER(all(dates),[FY16]>0 && Dates[Date]=max(Dates[Date]) ))
My sample data and sample pbix file
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
tringuyenminh92 : Hi , this is my sample data for FY16 .
Here is the visualization on the line chart using the above data. Right now on the axis , it is showing the month and year. When I hover over the graph I see the daily values.
I have another table for FY17 similar to FY16 . Now I want to show the FY17 data on the same chart above the FY16 graph . On the axis , I want to see only the month . On the legend I want to see FY16 and FY17.
Please help me in accomplishing this. Thanks.
Priya
Hi priyapalanki,
if you have 2 separate tables FY16 and FY17, it's easy now.
- Create Dates table for time pattern
Dates = CALENDARAUTO()
- Create Prev-Date Column
Prev - Date = DATEADD( Dates[Date],-1,YEAR)
- Union 2 separate tables FY16 -FY17 into 1 table:
u = UNION(FY16,FY17)
- Create 2 calculated measure to cumpute total amount of FY16 and FY17:
FY16 = CALCULATE(SUM(u[Amount]),FILTER(all(dates),Dates[Date]=MAX(Dates[Prev - Date]) ))
FY17 = CALCULATE(SUM(u[Amount]),FILTER(all(dates),[FY16]>0 && Dates[Date]=max(Dates[Date]) ))
My sample data and sample pbix file
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
tringuyenminh92 Thanks a lot for providing the solution . Would it be possible to see only the month on the axis ?
Hi priyapalanki,
As formula of 2 calcuated measure, it's computing by date level to show daily value in line chart, so far i could not make the X-Axis to show month level. It's so sorry to say that in this moment. :smileysad: