While Loop Predicting Planned Maintenance

[GeekyT]

Hi Everyone,

I need some help creating a While loop which can help predict planned maintenance (PM) dates. Here is an example of my data:

What I'm trying to do is to create a report where a user can enter a date range in the future and the report will tell them which PMs will be due during that date range. For example if a user chose 25th May 2021 to 25th Aug 2021 the report would tell them that both PMs 1 and 2 would be due.

I've done some research and I believe the answer is I need to create a While loop. I'm also aware that this isn't particularly simple in Power BI. I've read Greg Deckler's excellent article on For and While loops in DAX but I'm struggling to implement it in this case. Can anyone explain how I might do this?

[v-xulin-mstf · Accepted solution]

Hi @GeekyT

Try this in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY5BDsAgCAT/wtlkZbGmvMX4/29U24peyGRYNrQmKkksj8FMhUE3WnDBjlwHV9ClpyYcpoYmaPuU5YiHv0Eu1gzq2zPXtooyHBrHDv+QA39ro2Z9U2Zlkd4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PM ID No." = _t, cal_days = _t, #"Last PM" = _t, #"NEXT PM Due" = _t, #"PM lteration 2" = _t, #"PM lteration 3" = _t, #"PM lteration 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PM ID No.", Int64.Type}, {"cal_days", Int64.Type}, {"Last PM", type date}, {"NEXT PM Due", type date}, {"PM lteration 2", type date}, {"PM lteration 3", type date}, {"PM lteration 4", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PM ID No.", "cal_days"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Lteration Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

Then you get this table:

Try measure as:

Measure = 
var a=CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Lteration Date]<=MAX(StartDate[Date]) && ('Table'[Lteration Date] + 'Table'[cal_days])>=MAX(EndDate[Date])))
return
IF(
    a=0,
    MAX('Table'[PM ID No.]),
    BLANK()
)
    

Here is the output:

The pbix file is attached.

Best Regards,

Link

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

[GeekyT]

@v-xulin-mstf That's great! Thanks so much.

[amitchandak]

@GeekyT , Use an independent date table in Range

Create a measure like

Measure =

var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = minx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(countrows(Table), filter('Table', 'Table'[New PM Due] >=Min && 'Table'[New PM Due] <=Max ))

[GeekyT]

@amitchandak I'm sorry I wasn't clear. Your solution will give me the number of PMs due within the date range given the PM's current 'Next Due' dates. However I also need something which, for each PM ID No., will iterate through the next occurrences of that PM (using the cal_days) to check whether an occurence falls within the chosen date range. The point being someone can use the report to find out how many PMs they have to do during the time period chosen.

Here is an example I've created to show what I mean. The user has entered the start and end dates of the period they're interested in, and the report has identified that PMs 1 and 2 will have iterations during that period (highlighted in green and listed in E10 and E11):

I hope that is a little clearer, my apologies for not explaining correctly.