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 fiscal year needs to be formatted and populated based on the Reported Date column.  My formula is 

Fiscal Month/Yr = if('2700 WO Listing'[Covid vs Non-Covid (groups)]="Non-Covid"(FORMAT('2700 WO Listing'[Reported Date],"MMM-yyyy",'2700 WO Listing'[Scheduled Start],"MMM-yyyy)))
 
What is wrong with my syntax?
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")
                     )

     

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    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")
                     )

     

  • lkalawski's avatar
    lkalawski
    Resident Rockstar
    5 years ago

    ahhollan 

    you lost some parentheses, formulas and quotation marks. Try this:

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

     



     

    Proud to be a Super User.

    If I helped, please accept the solution

    and give kudos! 

     

  • 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.