Forum Discussion

RichJW's avatar
RichJW
Helper III
7 years ago
Solved

Date format in a DAX formula

Hi,

 

I have a column of data, which is in date format dd/mm/yy.

I need to change every date in the column that is earlier than 01/01/18, to show 01/01/18. So, if the date is 04/02/18, then it stays as it is, however if the date is 08/12/17, then it needs to show as 01/01/18.

 

I've tried various formulae with no success...

GanttStartDate = IF(Projects[ProjectStartDate].[Date]<01/01/18,01/01/18,Projects[ProjectStartDate].[Date])

GanttStartDate = IF(Projects[ProjectStartDate]<01/01/18,01/01/18,Projects[ProjectStartDate].[Date])

GanttStartDate = IF(Projects[ProjectStartDate]<01/01/18,01/01/18,Projects[ProjectStartDate])

 

Can anyone please advise a suitable formula?

 

Many thanks,

Rich

Need Help
  • petrovnikitamai's avatar
    petrovnikitamai
    7 years ago

    try this

    GanttStartDate = IF(Projects[ProjectStartDate]<DATE(2018,01,01),DATE(2018,01,01),Projects[ProjectStartDate])

    use dax function DATE()

2 Replies

  • petrovnikitamai's avatar
    petrovnikitamai
    Resolver V
    7 years ago

    try this

    GanttStartDate = IF(Projects[ProjectStartDate]<DATE(2018,01,01),DATE(2018,01,01),Projects[ProjectStartDate])

    use dax function DATE()

    • RichJW's avatar
      RichJW
      Helper III
      7 years ago

      You, my friend, are wonderful :manhappy:

       

      Thanks so much.

      I even like the touch of missing out the closing square bracket after projectstartdate, so I didn't just copy paste! :manhappy:

       

      Cheers,

      Rich