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

Share Improve this question Follow edited Nov 19, 2024 at 14:18 Máté Juhász 2,32522 gold badges2424 silver badges4444 bronze badges asked Nov 19, 2024 at 13:35 Yolanda CBYolanda CB 1566 bronze badges 1

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

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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