How to emulate Line Chart from Excel to Power BI

[smjzahid]

I have a Line Chart that I want to exactly emulate in Power BI. The table below shows sample data on which the report is to be built

Here is the LINE CHART in EXCEL, Please note that date on X Axis in excel chart (which has shows value for every 7 days)

However, when I try this Line Chart in Power BI it do not show the data (meaning days split every 7 days) as above excel visual, I have tried to change the X AXIS from (Categorical to Continous) but do not get it to work.

I have the date column formatted as date and value or the number column as Whole number,

Any idea how do I get this to show data as shown in excel

		02/01/21	03/01/21	04/01/21	05/01/21	06/01/21	07/01/21	08/01/21	09/01/21	10/01/21	11/01/21	12/01/21	13/01/21	14/01/21	15/01/21	16/01/21	17/01/21	18/01/21	19/01/21	20/01/21	21/01/21	22/01/21	23/01/21	24/01/21	25/01/21	26/01/21	27/01/21	28/01/21	29/01/21	30/01/21	31/01/21	01/02/21	02/02/21
Piling	Baseline									0	3	8	13	18	23	23	23	26	31	36	41	45	45	45	45	45	45	45	45	45	45	48	53
Actual				1	1	7	6	6	6	6	6	6	6	23	23	23	21	28	29	36	42	48	48	51	59	59	60	67	67	67	67	67
Pile Caps	Baseline
Actual
Shell Boxes	Baseline
Actual
Cill Beams	Baseline
Actual
Bearings	Baseline
Actual
Y Beams	Baseline
Actual

[PaulDBrown]

@smjzahid 

1) create a new column in the date table with the following (If you prefer the day reference to be the last day of the week use MAX instead of MIN)

2) Create the line chart using this "7 day axis" column. Set the axis to categorical:

[smjzahid]

Hi @PaulDBrown ,

Thanks for your reply, How have you created the WEEK NUMBER and YEAR columns in your date table as I do not have those columns in my table, I just have a date column (that too not even a complete year). The Min date is 10/2020 and MAX is 05/2021

[PaulDBrown · Accepted solution]

@smjzahid 

To create a date table:

Let's say your date field in your fact table is 'FactTable'[Date]

Go into the Modeling option in the ribbon and select new table. Now type in the following code:

DateTable = 
VAR MinDate = MIN(FactTable[Date])
VAR MaxDate = MAX(FactTable[Date])
RETURN
ADDCOLUMNS(CALENDAR(MinDate, MaxDate), 
"Year", YEAR([Date]),
"Month Numb", MONTH([Date]),
"Month", FORMAT([Date], "MMM"),
"Week Number", WEEKNUM([Date])
)

If you already have a date table with continuous dates covering the range of dates in your model, you can add new columns using the code for each column as above. For example,

Year = YEAR(DateTable[Date])

and

Week Number = WEEKNUM(DateTable[Date])

 (apologies since my settings are in spanish. Hence the month names)

Now add a new column to this table in the ribbon and add this code:

7 Day Axis = CALCULATE(MIN(DateTable[Date]), ALLEXCEPT(DateTable, DateTable[Week Number], DateTable[Year]))

Finally go to the modeling pane and join both date fields in a one-to-many relationship

You have now created a Date Table and you should use the fields from this table in your visuals, slicers, filters, measures etc. You can also now use Time Intelligence functions for your calculations using this table.

[negi007]

@smjzahid did you try the option of creating a new column which can be used in your x-axis like below

Week = 'Date'[Date].[Day]& "/" & 'Date'[Date].[MonthNo] & "/" & RIGHT('Date'[Date].[Year],2) & "" & "/W"& WEEKNUM('Date'[Date],2)

your line chart could be like this

[smjzahid]

@negi007 , Thanks for your reply, it does not show the data as per the Excel chart as shown above, Look how there is a interval of every 7 days on the excel chart between 2 dates, your formuala does not achieve this