Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

While Loop Predicting Planned Maintenance

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 u...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous

     

    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.

     

     

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , 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 ))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.