Need help with management dashboard workorders

[kv41282]

Dear all, i am new here, and i am learning how to use this forum.  sorry for that. 

I have a database (reduced version in excel attachment) with workorders for which I would like to create a management dashboard.

I want a number of graphs on the dashboard, see also examples of images in the attachments:

• trend graph Registered vs completed (in Dutch ‘Aangemeld vs. Afgesloten’).; How many workorders were reported in a month but also how many were closed in that month. This shows the growth or decrease in the last months.
• trend graph workstock (feasibility) (in Dutch ‘Werkvoorraad, Haalbaar/ niet haalbaar en zonder streefdatum); Per month also the number of workorders that shows how many were feasible [light blue], not feasible [dark blue] or had no target date [orange] in that month based on the planned target date.
• trend graph Timeliness (in Dutch ‘Tijdigheid’ ; op tijd, te laat, geen streefdatum); per month the number and percentage of workorders that were on time based on the planned target date. on time/too late, without target date with a trendline and a normline.

In the excel I have a database with various data of the workorders including the notification date {melddatum), a planned completion date and an actual completion date.

With those data fields I want to make the evaluations

The example graphs give an example in Year&Month. but the wish is also to be able to display them in Year&Weeks or years.

The difficulty for me is the date calculations that are displayed dynamically but are fixed in my database. Because something that is reported in January but only reported in June runs through several months. and that now distorts my graphs.

It would be great if you have input and can help me move forward. Thanks in advance.

ID	context	status	melddatum	geplande gereeddatum	afdeling	Creatie datum	werkelijk gereeddatum	werkelijk gesloten
JobId	JobContext	JobRecStatus	JobReportDate	JobSvcTargetdate	JobDepId	JobPrsId	JobRecCreateDate	JobFinishDate	JobCloseDate
1169677	128	32	3-7-2023  08:03:43	10-7-2023  08:03:43	C	US0030	3-7-2023  08:03:56	3-7-2023  08:31:56	18-7-2023  00:31:10
1169680	128	32	3-7-2023  08:13:57	10-7-2023  08:13:57	C	US0030	3-7-2023  08:14:06	3-7-2023  09:44:13	18-7-2023  00:31:10
1169686	128	32	3-7-2023  08:27:07	10-7-2023  08:27:07	C	US0030	3-7-2023  08:27:30	3-7-2023  10:44:39	18-7-2023  00:31:10
1169688	128	32	3-7-2023  08:33:15	10-7-2023  08:33:15	C	US0030	3-7-2023  08:33:16	3-7-2023  08:42:46	18-7-2023  00:31:10
1169691	128	32	3-7-2023  08:36:58	10-7-2023  08:36:58	C	US0030	3-7-2023  08:37:01	3-7-2023  08:50:15	18-7-2023  00:31:10
1169693	128	32	3-7-2023  08:37:44	10-7-2023  08:37:44	C	US0030	3-7-2023  08:38:55	3-7-2023  08:53:36	18-7-2023  00:31:09
1169698	128	32	1-7-2023  08:30:00	10-7-2023  00:00:00	C	US0030	3-7-2023  08:46:44	3-7-2023  08:50:23	18-7-2023  00:31:09
1169701	128	32	1-7-2023  22:15:00	10-7-2023  00:00:00	C	US0030	3-7-2023  08:48:25	3-7-2023  08:50:40	18-7-2023  00:31:09
1169702	128	32	3-7-2023  08:49:08	10-7-2023  08:49:08	C	US0030	3-7-2023  08:49:15	3-7-2023  10:13:55	18-7-2023  00:31:09
1169703	128	32	3-7-2023  08:50:02	10-7-2023  08:50:02	C	US0030	3-7-2023  08:50:12	3-7-2023  10:22:38	18-7-2023  00:31:08
1169714	128	32	3-7-2023  08:57:37	10-7-2023  08:57:37	C	US0030	3-7-2023  08:57:44	3-7-2023  08:59:32	18-7-2023  00:31:08
1169723	128	32	3-7-2023  09:08:50	10-7-2023  09:08:50	C	US0030	3-7-2023  09:09:04	3-7-2023  09:49:09	18-7-2023  00:31:07
1169729	128	32	3-7-2023  09:19:12	10-7-2023  09:19:12	C	US0030	3-7-2023  09:19:50	3-7-2023  09:25:21	18-7-2023  00:31:07
1169733	128	32	3-7-2023  09:22:49	10-7-2023  09:22:49	C	US0030	3-7-2023  09:23:18	3-7-2023  09:24:41	18-7-2023  00:31:07

[kv41282]

First of all, thanks for your reaction. 

Unfortunately I can't open you pbix with my version of Power BI. 

But how does it works with more data columns . Because in the first grafpic that I want, I have per month two bars with "Open"and "Closed" Workorders. 

Best Regards,

Kim 

[Anonymous · Accepted solution]

Hi @kv41282,

According to your statement, I think your requirement is to show dynamic X axis like Year-Month/Year-Week/Year and so on.

As far as I know, you can create a DimDate table and relate it with your data table by [Date] column.

DimDate =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "MonthSort", MONTH ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 2 ),
    "YearMonth", FORMAT ( [Date], "YYYY-MMM" ),
    "YearMonthSort",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "YearWeek",
        FORMAT ( [Date], "YYYY" ) & "-" & "Week" & ""
            & WEEKNUM ( [Date], 2 ),
    "YearWeekSort",
        YEAR ( [Date] ) * 100
            + WEEKNUM ( [Date], 2 )
)

My Sample:

Then you can use Field parameter to achieve your goal.

For reference: Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.