Forum Discussion

priyapalanki's avatar
priyapalanki
Frequent Visitor
9 years ago
Solved

Comparing previous and current year data on a line chart

Hi

 

I want to compare the daily trend for FY 16 ( Jul 1st 2015 to June 30th 2016) and FY17( July 1st 2016 to June 30th 2017) on a line chart in two graphs ,  on the x-axis I want to see only the month and when I hover over the graph i want to see the daily values . The legend should dispaly Fiscal Year (FY16 , FY17).

Can someone help me pn how to accomplish this scenario ?

 

Thanks,

Priya

 

 

Need Help
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    Hi priyapalanki,

     

    if you have 2 separate tables FY16 and FY17, it's easy now.

     

    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

5 Replies

    • priyapalanki's avatar
      priyapalanki
      Frequent Visitor
      9 years ago

      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

       

       

       

       

       

      • tringuyenminh92's avatar
        tringuyenminh92
        Memorable Member
        9 years ago

        Hi priyapalanki,

         

        if you have 2 separate tables FY16 and FY17, it's easy now.

         

        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