IF Formula and Concatenate

[RanjanThammaiah]

Hi Team,

Can some one help me with the below Excel formula.

i have tried building this but i was geting the argumental error.

=IF(Outcome ="lost","Lost",IF(Outcome ="declined","Declined",IF(OUTCOME="won",CONCATENATE(FiscalMonthofClose," ","wins"),IF(AnticipatedWinDate < 28-06-2019,"Past Due",IF(AnticipatedWinDate < 02-08-2019,"Jul FC",IF(AnticipatedWinDate < 30-08-2019,"Aug FC",IF(AnticipatedWinDate < 27-09-2019,"Sept FC",IF(AnticipatedWinDate < 01-11-2019,"Oct FC",IF(AnticipatedWinDate < 27-12-2019,"Nov FC",IF(AnticipatedWinDate < 31-01-2020,"Dec FC",IF(AnticipatedWinDate < 28-02-2020,"Jan FC",IF(AnticipatedWinDate < 27-03-2020,"Feb FC",IF(AnticipatedWinDate < 01-05-2020,"March FC",IF(AnticipatedWinDate < 29-05-2020,"April FC",IF(AnticipatedWinDate < 03-07-2020,"May FC",IF(AnticipatedWinDate < 03-07-2021,"June FC","FY20"))))))))))))))))

Kindly let me know if i can add this to BI.

Regars,

Ranjan

[v-shex-msft]

Hi @RanjanThammaiah ,

This issue should more related to your dax formula concatenate function part(concatenate can only used to merge two text), you have typing wrong format. I fix this issue and share the sample below, you can try it if works.

Measure =
IF (
    Outcome = "lost",
    "Lost",
    IF (
        Outcome = "declined",
        "Declined",
        IF (
            OUTCOME = "won",
            CONCATENATE ( FiscalMonthofClose, " wins" ),
            IF (
                AnticipatedWinDate < 28 - 06 - 2019,
                "Past Due",
                IF (
                    AnticipatedWinDate < 02 - 08 - 2019,
                    "Jul FC",
                    IF (
                        AnticipatedWinDate < 30 - 08 - 2019,
                        "Aug FC",
                        IF (
                            AnticipatedWinDate < 27 - 09 - 2019,
                            "Sept FC",
                            IF (
                                AnticipatedWinDate < 01 - 11 - 2019,
                                "Oct FC",
                                IF (
                                    AnticipatedWinDate < 27 - 12 - 2019,
                                    "Nov FC",
                                    IF (
                                        AnticipatedWinDate < 31 - 01 - 2020,
                                        "Dec FC",
                                        IF (
                                            AnticipatedWinDate < 28 - 02 - 2020,
                                            "Jan FC",
                                            IF (
                                                AnticipatedWinDate < 27 - 03 - 2020,
                                                "Feb FC",
                                                IF (
                                                    AnticipatedWinDate < 01 - 05 - 2020,
                                                    "March FC",
                                                    IF (
                                                        AnticipatedWinDate < 29 - 05 - 2020,
                                                        "April FC",
                                                        IF (
                                                            AnticipatedWinDate < 03 - 07 - 2020,
                                                            "May FC",
                                                            IF ( AnticipatedWinDate < 03 - 07 - 2021, "June FC", "FY20" )
                                                        )
                                                    )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

What logic you used to calculate financial date? Can you please share more detail? It can used to reduce the complexity of your formula and make it more 'dynamically'.

Regards,

Xiaoxin Sheng

[RanjanThammaiah]

We have set of fixed dates which doesnt change.

It depends on my Company Financial close dates. But this will be fixed for an year from now.

Dates	Financial Close
28-Jun-19	Past Due
02-Aug-19	July
30-Aug-19	August
27-Sep-19	September
01-Nov-19	October
29-Nov-19	November
27-Dec-19	December
31-Jan-20	January
28-Feb-20	February
27-Mar-20	March
01-May-20	April
29-May-20	May
03-Jul-20	June

[Anonymous]

take a look at using the switch function:

https://docs.microsoft.com/en-us/dax/switch-function-dax