Machine servicing schedule

DAX DAX Calculations
1

I have a table which stores details of machines and I want to produce a service schedule counting the number of machines scheduled for a service in 30, 60, 90, 120+ bands.

I have created a measure which does not work properly. The example below is for those machines requiring a service between 31 to 60 days.

Service 60 Days =
COUNTX(Fans,
IF(Fans[First Service Date] - TODAY() > 30 && Fans[First Service Date] - TODAY() <= 60, 1, 0))

2

Hi @PaulBoyes. To help the forum members visualize your issue and target, please provide your work-in-progress PBIX file (with sanitized or sample data, if necessary) along with a marked-up screenshot of the visual you’re trying to achieve or an Excel mock-up of your desired outcome.
Greg

3

@PaulBoyes,

EDNA No PBIX Uploaded Motto GIF

Thanks
Jarrett

2 Likes
4

Paul Boyes Machines.pbix (1.2 MB)
Sorry about that. I have now attached a pbix file.

In the Key Measures group there are examples of the various measures.

The measure Service 30 Days is an example.

What I want to achieve is the actual date of the next scheduled service in the Warranty and Service tab in the appropriate column.

I hope this helps.

Paul

5

Hi @PaulBoyes. A few things right off the bat. Your model does not have a DATES table; please add one and mark it as such to enable you to use the DAX time intelligence functions in your model.

The M code for an excellent DATES table is available from Enterprise DNA:

Then, a data modelling refactor and/or a review of your data is necessary. (I noticed, for example, that there are very few records which are actually linked between your [Fans] table and your [Transform-ITData] tables.)

Once the DATES table has been added, marked as such, your model and/or data has been reviewed and revised as necessary, repost if you’re still having issues with your measures.

Greg

6

Hi

I attach a revised pbix file with a date table.

Paul Boyes Machines.pbix (1.2 MB)

Trust it is OK

Paul

7

Hi @PaulBoyes. Progress with the DATES table, but the modelling and data review effort is still needed from your side. The DATES table as well is not modelled to your data (and the main field needs to be “Date”, not “Date/Time”), and the data issues remain. Please review the data that will be used for linking and adjust as necessary.
Greg

8

Hi

I have added the dates table as suggested and an updated pbix file is below.

The problem that we have with the data is that this is a proof of concept we are preparing for a prospective customer so we have very little.

I would still appreciate assistance with the DAX date calculations.

Best regards

Paul

Paul Boyes Machines.pbix (1.3 MB)

9

Hi @PaulBoyes. So you file still needs some setup before calculations can begin, including:

  • disable option for Current File \ Data Load \ Time Intelligence for Auto Date-Time
  • in Data View, change data type of Fans[First Service Period] from Text to Whole Number
  • in Data View, change data type of Fans[Install Date] from Text to Date
  • in Model View, add relationship Dates[Date] --> Fans[Install Date]

Once those setup items are complete, you can then:

  • add a table for [Fans]
  • add Fans[Serial No]
  • add Fans[First Service Period]
  • to get [First Service Date], add Fans[First Service Period] to Fans[Install Date]; format as dd-mmm-yyyy
M-First Service Date = 
MAX( Fans[Install Date] ) + MAX( Fans[First Service Period] )
  • add [M-Service XX Days] measures for 30, 60, 90, 120, over 120 days; e.g.,
M-Service 60 Days = 
VAR _DaysUntilService = DATEDIFF( TODAY(), [M-First Service Date], DAY )
VAR _Result = SWITCH( TRUE(),
    _DaysUntilService <= 0, BLANK(),
    _DaysUntilService > 30 && _DaysUntilService <= 60, 1,
    0
)

RETURN
_Result

Here’s what I came up with:

eDNA Forum - Machine Service Schedule
eDNA Forum - Machine Service Schedule1783×646 98.8 KB

Hope this helps.
Greg
eDNA Forum - Machine Service Schedule.pbix (1.2 MB)

10

Thank you so much for your help.

This has been very helpful in the short term because it solved an urgent issue.

During Spring 2020 I put a large effort in getting to grips with Power BI, and DAX in particular.

Sadly, after that I had a number of months where I was unable to do too much with it.

So the main lesson here is if you want to retain skills you have to make time to keep them sharp by staying involved.

Thanks again

Paul