Forum Discussion

ahhollan's avatar
ahhollan
Helper I
5 years ago
Solved

Simple if(format syntax question

I have a column with values of Non-Covid and Covid. If the value is Covid, the fiscal year needs to be formatted and populated based on the Scheduled Start column. If the value is Non-Covid, the fisc...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    ahhollan 

    If this is a calculated column in a table, try:

     

     

    Fiscal Month/Yr = IF(
                     '2700 WO Listing'[Covid vs Non-Covid (groups)]="Non-Covid", 
                    FORMAT('2700 WO Listing'[Reported Date],"MMM-yyyy"),
                       FORMAT('2700 WO Listing'[Scheduled Start],"MMM-yyyy")
                     )

     

     

    If you are using this in a measure, try:

     

    Fiscal Month/Yr = IF(
                    MAX( '2700 WO Listing'[Covid vs Non-Covid (groups)]) = "Non-Covid", 
                    FORMAT('2700 WO Listing'[Reported Date],"MMM-yyyy"),
                       FORMAT('2700 WO Listing'[Scheduled Start],"MMM-yyyy")
                     )

     

DataZoe's avatar
DataZoe
Microsoft Employee
5 years ago

ahhollan this syntax should work for calculated column:

 

Fiscal Month/Yr =
IF (
    '2700 WO Listing'[Covid vs Non-Covid (groups)] = "Non-Covid",
    FORMAT ( '2700 WO Listing'[Reported Date], "MMM-yyyy" ),
    FORMAT ( '2700 WO Listing'[Scheduled Start], "MMM-yyyy" )
)

 

but it would not work for a measure. 

 

also, the way you have it written will make it a text column. You could also remove the format() from each part and just return each them as dates, then format the column into the desired format if you want it to still behave as a date column.