Forum Discussion

asjones's avatar
asjones
Icon for Helper V rankHelper V
5 years ago
Solved

Max Date Math Issues In Power Query

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 exc...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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's avatar
asjones
Icon for Helper V rankHelper V
5 years ago

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's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

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