Find the minimum within a defined and limited intervall of a column

[Clout]

Hello guys,

I want to make a new calculated column on the base of another column. So for every single row in the column I want to search for the smallest number - 10 days before and 10 days after the looked day incl. the day itself (21 days total). But it should be possible with the first day too of the table, so the second row in the column is only searching for the minimum within 1 day before and 10 days after (12 days total).

In addition of it, I want to multiplicate the min with 1,2. 

I need this column for a further conditional column to see if a value is smaller or equal of the calculated column with the respective minimas. Is it maybe possible to combine the two steps within one column to safe performance?

I would be very thankfull when someone can help me out.

[Anonymous]

@Clout 

Using M is not that hard after all 🙂 It's mostly through GUI that you'll be using it and the commands are rather simple and self-describing. Not sure if you could do the above entirely through GUI but I can immediately see what M one would have to write (a tiny bit only) to make it the way you want. I'll write the M you need and paste it here. And you, in the meantime, please start learning Power Query (and M). There's a ton of material about it on the net. Just google for it... It'll be time well invested since M is also used in Azure workflows and everything to do with Power BI regarding data cleansing and preparation.

[Anonymous]

Hi @Clout 

Why do you want to do that in DAX? Such calculations should be performed in M (Power Query) unless you've got a very compelling reason not to do so. And in Power Query it'll be more efficient.

[Clout]

Oh okay, Im new to Power BI and I thought such columns are only possible trough "calculated columns" which is dax based. So I have to do it trough "costum column" right with the language "M"? 

Unfortunately I dont have experience in "M" either.

[Anonymous · Accepted solution]

@Clout 

Here's the M that does what you want. Paste it into the Advance Editor and inspect the steps. Then use the code to transform your data. The only manual bit is the calculation of the "10/10 Minimum" column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZI7jgJBDETvMjEj2e7/BQj2CogAJFLuHzKSbVS1TPrkTz933W6b6P73eO8mIttlm2O7Xw5oCFdxWBBWZxVZc9aQqTnsCEdUDpq4HE6EPR60aKY/SIWW+0wlnzEdks/0Jyn5LF+kJFTUIRlNcUhGtTskox6VZNSikoycmZww8lG/kZGPuY+xT7TXk93WzmaSj0Ul+aifw8hnRfv6TUchH/NrFBKy+F/dr6/nv2wZsvifI4QAtWcKAfaSMQTYasYQ2yVjCHBYxhCgacYQF7WMIcDSM4ZYKRlDnDkzhr+LlIzGN4a4SDOGaGQZQ5z5jSHAqRlDbB+ZQ6w87nn/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Add 10 Days Before" = Table.AddColumn(#"Changed Type", "Date10DaysBefore", each Date.AddDays([Date], -10)),
    #"Add 10 Days After" = Table.AddColumn(#"Add 10 Days Before", "Date10DaysAfter", each Date.AddDays([Date], 10)),
    #"Add Minimum" =
        Table.AddColumn(#"Add 10 Days After", "10/10 Minimum",
            each
            let
                LowerBound = [Date10DaysBefore],
                UpperBound = [Date10DaysAfter],
                Minimum =
                    List.Min(
                        Table.SelectRows(
                            #"Add 10 Days After", 
                            (r) => LowerBound <= r[Date] and r[Date] <= UpperBound
                        )[Value]
                    )
            in
                Minimum
        ),
    #"Removed Columns" = Table.RemoveColumns(#"Add Minimum",{"Date10DaysBefore", "Date10DaysAfter"})
    in
        #"Removed Columns"

[Clout]

Hello @Anonymous, thank you very much, it now works like hoped 🙂

I did paste the code from row to row in the Advance Editor to see what every code row does and I think a little bit I checked M more. 

I have to do a Dashboard trough Power BI as my Bachelor Thesis and Im very limited in time, so unfortunately I don't have much time do deepen M or DAX. But you helped me so much out with this code, thanks again!