Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to Solution.
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.
Proud to be a Super User!
Paul on Linkedin.
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:
Proud to be a Super User!
Paul on Linkedin.
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
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.
Proud to be a Super User!
Paul on Linkedin.
@smjzahid did you try the option of creating a new column which can be used in your x-axis like below
your line chart could be like this
Proud to be a Super User!
@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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |