Monthly, Bi Monthly and Quaterly due maintenance visits that can be covered in one table output.

[Raj2410]

Hi Team,

Like I mentioned in the subject line.

I have some data where I have multiple equipments that have maintenance visits due and every maintenance visit has a different criteria.

Equipment 1x1 need Monthly Maintenance vist.

Equipment 2x2 need Bi Monthly Maintenance visit.

Equipment 3x3 need Quaterly Maintenance visit.

For every missed maintenace visit there is a penalty also.

Thats my source or you can say table 1.

Equipment No.	Class	CUSTOMER	Work due	Applicable penalty
1x1	A	RM	Monthly	$                    100
2x2	B	RM	Bi Monthly( Once in two months)	$                    200
3x3	C	RM	Quaterly (Once in 3 months)	$                    300

Now I have the second table where I have the result of all 12 months for all 3 equipments.

Equipment No.	Class	CUSTOMER	WORK DATE	WORK HOURS
1x1	A	RM	January	5
1x1	A	RM	February	0
1x1	A	RM	March	5
1x1	A	RM	April	0
1x1	A	RM	May	10
1x1	A	RM	June	0
1x1	A	RM	July	5
1x1	A	RM	August	0
1x1	A	RM	September	10
1x1	A	RM	October	5
1x1	A	RM	November	5
1x1	A	RM	December	0
Equipment No.	Class	CUSTOMER	WORK DATE	WORK HOURS
2x2	B	RM	January	5
2x2	B	RM	February	0
2x2	B	RM	March	5
2x2	B	RM	April	5
2x2	B	RM	May	0
2x2	B	RM	June	0
2x2	B	RM	July	2
2x2	B	RM	August	9
2x2	B	RM	September	4.5
2x2	B	RM	October	0
2x2	B	RM	November	0
2x2	B	RM	December	0
Equipment No.	Class	CUSTOMER	WORK DATE	WORK HOURS
3x3	C	RM	January	1
3x3	C	RM	February	2
3x3	C	RM	March	0
3x3	C	RM	April	2
3x3	C	RM	May	0
3x3	C	RM	June	0
3x3	C	RM	July	4.5
3x3	C	RM	August	9
3x3	C	RM	September	0
3x3	C	RM	October	0
3x3	C	RM	November	0
3x3	C	RM	December	0

Now I want to take this data in Power BI and create mutiple visuals.

Primary visual I want to create will be a table that can show result of both table something similar to below.

I would also like to have a slicer in power Bi so when I select the month of january it should show me results of all 3 equipments.

My main aim is to have - Monthly, Bi Monthly and Quaterly due maintenance visits covered in one table output and display result in one table.

Please suggest.

Thank you

[v-tangjie-msft · Accepted solution]

Hi @Raj2410 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns and a slicer table.

Please refer to my pbix file.

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Raj2410]

@v-tangjie-msftthank you this is good solution fore now.

[Raj2410]

Hi is there someone in the community that can help me with this one.