Forum Discussion
Helper VHow to emulate Line Chart from Excel to Power BI
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 |
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.
5 Replies
PaulDBrownCommunity Champion
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:
smjzahidHi 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
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.
