Comparing turnover in curved line chart

[aletho]

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 🙂   

date	turnover
01-01-2016	105
02-01-2016	102
03-01-2016	104,4
04-01-2016	105
05-01-2016	105
06-01-2016	106
07-01-2016	112
08-01-2016	140,5
09-01-2016	150
10-01-2016	152
11-01-2016	153
12-01-2016	140
01-02-2017	126
02-02-2017	122,4
03-02-2017	125,28
04-02-2017	126
05-02-2017	126
06-02-2017	127,2
07-02-2017	134,4
08-02-2017	168,6
09-02-2017	180
10-02-2017	182,4
11-02-2017	183,6
12-02-2017	168
01-01-2018	163,8
02-01-2018	159,12
03-01-2018	162,864
04-01-2018	163,8
05-01-2018	163,8
06-01-2018	165,36
07-01-2018	174,72

[TomMartens · Accepted solution]

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]

Thanks, Tom! This is really helpful 🙂