Max Date Math Issues In Power Query

[asjones]

I wrote an Excel formula that I would like to convert ot Power Querey, but  I cant get the List.Max or Table.Max to work thust the rest fails also.

I want a new column that works simular to this excel formula.

=IF( MAX([Dates] ) - 7 < [@Dates], " 7 days", "-")

I know a few ways to do it, but my real goal will be the above formula plus this one 
=IF( MAX([Dates] ) - 14 < [@Dates], " 14 days", "-")

and if it is within 7 days I would have two rows for the date one for 7 Days and one for 14 days out. I have a process i figured out how to do it with 2 columns then an append and dedupe. However if someone has something better would love to hear that also.

thanks

Alan

[v-kelly-msft · Accepted solution]

Hi @asjones，

Since you need 2 columns based on the max date,I would suggest you first create a column to get the max date,using below M codes:

=Table.Max(#"Changed Type","Date")

Then create another 2 columns to get 7days and 14days:

=if [Date]>Date.AddDays([Max date.Date],-7) then "7 days" else null

=if [Date]>Date.AddDays([Max date.Date],-14) then "14 days" else null

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[asjones]

@v-kelly-msftthanks for the reply... that is probably the cleanest option and it should also work cleanly in Excel Power Query also.

thanks

Alan

[v-kelly-msft]

Hi  @asjones ,

Glad to help.🙂

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Here's a way that'll work nice and fast. Let's say your current final step is named LastStep.  Just add a new step named MaxDate7. Then in the Formula Bar, type:

= Date.AddDays(List.Max(List.Buffer(LastStep[Dates])),-7)

Then add your column; add a new step named "NewColumn", then in the formula bar, type:

= Table.AddColumn(LastStep, "DateCompare", each if [Date] > MaxDate7 then "7 Days" else "-", type text)

This way your Max Date-7 days is computed once and stored as a variable. Having it in the in statement will cause you trouble. If you want to add the Date-14 variable, just add another step before you add your columns named MaxDate14, and that would be:

= Date.AddDays(MaxDate7, -7)

--Nate

[edhans]

Just be careful with List.Buffer on a large list. Put a few hundred thousand or million records in it, and it could cause Power Query to consume all RAM on the PC. I thought about doing it that way, but I still contend Power Query isn't the right place for this unless you know it is going to be a relatively small dataset.

[asjones]

@edhansthanks for the reply this looks fairly processor intensive ...especially as I wanted to also do a 14 day version  then combine them.... it seems like this is a case where Excel may be better? What do you think?

[edhans]

It depends. DAX may be better. Depends on where you need the output to be. This is the same thing in a DAX Calculated Column. This is one of the rare times I'd recommend a calculated column if you cannot do this efficiently in your source data. 

DAX Version = 
VAR varMaxDate = MAX('Table'[Date])
RETURN
IF(
    varMaxDate -7 < 'Table'[Date],
    "7 Days",
    "-"
)

[edhans]

You can do this @asjones - this will work fine on relatively small record sets, thousands of rows. It will not work on tens of thousands or higher.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31DcyMDJUitUBcYyQOcbIHBNkjikyxwyZY47MsUDmWCJzDA1QeChuMERxhCHMFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = 
        Table.AddColumn(
            #"Changed Type", 
            "Date Comparison", 
            each
                let
                    varDate = [Date]
                in
                if Date.AddDays(List.Max(#"Changed Type"[Date]), -7) < varDate then "7 Days" else "-"
            )
in
    #"Added Custom"

This is what it returns with dates from July 1 - July 13 in my sample data.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If you need more help, please give us some data to work with with expected results.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.