Forum Discussion

aletho's avatar
aletho
Icon for Helper I rankHelper I
8 years ago
Solved

Comparing turnover in curved line chart

Hi all

Hope you're enjoying your weekend.  

 

I am trying to make a graph like the one below with similar data as below the graph. The data I am working with is running live from my SQL server, so I made the example below to visualize what I want to do in Power Bi.

 

I have different data running over the last ten years, from financial numbers to everything you find it google analytics. 

What I would find very useful is having graphs like the one I made in Excel, where I can select different years to easily compare specific months over time.

 

Any answers are appreciated

thanks :)   

 

 

dateturnover
01-01-2016105
02-01-2016102
03-01-2016104,4
04-01-2016105
05-01-2016105
06-01-2016106
07-01-2016112
08-01-2016140,5
09-01-2016150
10-01-2016152
11-01-2016153
12-01-2016140
01-02-2017126
02-02-2017122,4
03-02-2017125,28
04-02-2017126
05-02-2017126
06-02-2017127,2
07-02-2017134,4
08-02-2017168,6
09-02-2017180
10-02-2017182,4
11-02-2017183,6
12-02-2017168
01-01-2018163,8
02-01-2018159,12
03-01-2018162,864
04-01-2018163,8
05-01-2018163,8
06-01-2018165,36
07-01-2018174,72

 

Show and Tell
Tutorial Requests
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

    I created the following chart

    adding some stuff to the table you provided.

     

    Here you will find the PBIX file

     

    I did the following I created a separate date table using this simple DAX statement, a separate date table is always a good idea not to say a best practice:

    Calendar = 
ADDCOLUMNS(
    CALENDAR("2016-01-01", "2018-07-31")
    ,"Month Name", FORMAT(''[Date], "MMM")
    ,"Month No", MONTH(''[Date])
    ,"Year", YEAR(''[Date])
)

    I used the column "Month No" to order the column "Month Name"

    In the "Data" view select "Sort by Column" from the "Modeling" menu

     

    And I also created a relationship between the calendar tabel (one side) and the fact tabel (many side) - that's it :-)

     

    Regarding the information "running live from my SQL Server" I guess you mean the connection is either "Direct Query" (the data is coming from the relational engine" or "Live Connection" if the data is hosted in SQL Server Analysis Services.

     

    The solution I described above is relying on the existence of a calendar table, this table currently can just be created in Power BI if the data is imported, this will change as soon as the preview feature "Composite Model" will become generally available.

     

    So eihter you import the data (then you have to consider how often the data needs to be refreshed) or ask the people that are owning the SQL Server data to create a Calendar table inside the SQL Server database,

     

    Regards,

    Tom

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    Hey,

    I created the following chart

    adding some stuff to the table you provided.

     

    Here you will find the PBIX file

     

    I did the following I created a separate date table using this simple DAX statement, a separate date table is always a good idea not to say a best practice:

    Calendar = 
ADDCOLUMNS(
    CALENDAR("2016-01-01", "2018-07-31")
    ,"Month Name", FORMAT(''[Date], "MMM")
    ,"Month No", MONTH(''[Date])
    ,"Year", YEAR(''[Date])
)

    I used the column "Month No" to order the column "Month Name"

    In the "Data" view select "Sort by Column" from the "Modeling" menu

     

    And I also created a relationship between the calendar tabel (one side) and the fact tabel (many side) - that's it :-)

     

    Regarding the information "running live from my SQL Server" I guess you mean the connection is either "Direct Query" (the data is coming from the relational engine" or "Live Connection" if the data is hosted in SQL Server Analysis Services.

     

    The solution I described above is relying on the existence of a calendar table, this table currently can just be created in Power BI if the data is imported, this will change as soon as the preview feature "Composite Model" will become generally available.

     

    So eihter you import the data (then you have to consider how often the data needs to be refreshed) or ask the people that are owning the SQL Server data to create a Calendar table inside the SQL Server database,

     

    Regards,

    Tom

    • aletho's avatar
      aletho
      Icon for Helper I rankHelper I
      8 years ago

      Thanks, Tom! This is really helpful :)