I want to generate a column. that shows as YTD if the date is the last day of the previous month or before and FCT otherwise.

This Dax code is giving me errors.

First tried Today() and then realised that's not what I want :)

Added Custom" = Table.AddColumn(#"Change Type4","YTD Date", each if [Date.Custom] <= Today() then "YTD" else "FCT")

I want to generate a column. that shows as YTD if the date is the last day of the previous month or before and FCT otherwise.

This Dax code is giving me errors.

First tried Today() and then realised that's not what I want :)

Added Custom" = Table.AddColumn(#"Change Type4","YTD Date", each if [Date.Custom] <= Today() then "YTD" else "FCT")

• date
• dax

• The above isn't DAX, it's Power Query. – Sam Nseir Commented Nov 19, 2024 at 15:31

2 Answers 2

Try the following:

= Table.AddColumn(#"Changed Type", "YTD Date", each if 
  DateTime.Date([Date]) <= DateTime.Date(
    Date.EndOfMonth(
      Date.AddMonths(
        DateTime.FixedLocalNow(), -1
      )
    )
  ) then "YTD" else "FCT")

• Get current date/time
• Subtract a month
• Get the last date of the month
• Convert both sides to dates

For what it is worth, you can do similar in DAX via EOMONTH function.

In power query, we don't use today() function to get current date you can try this below function to get current date

= Date.From(DateTime.LocalNow())

then replace the formula with your today() function in your M coding and have a try

enter link description here