Need help with my Formula

[RonaldvdH]

Ive got a formula with which I need some help.

This formula needs to do a couple of things but maybe there is a better way

Basically I want to determine a 'type'  of connection, an excisting one, a new one, one that has been migrated (=Migratie) and one that has been terminated (=Opzegging)

The first to rows of the formula are working, i get the results for Migratie and Opzegging but the last part of the formula always return a blank value.

The problem (I think) is the variable StartDateActive, what i'm basically trying to do is use the Patch Start Date and determine if that date is in THIS month (and year)

Obviously the port in my formula that says "24-dec" should ben monthly updates by hand but i'm guessing you know another solution.

Important to know is that this formula doesn't return an error it just doesn't find any rows that matches the last condition with the Switch function in it

Aansluittype = 
VAR StartDateActive = FORMAT('PH-data'[Patch start date],"YY-MMM")

RETURN
    IF(
    'PH-data'[Fiber]=1,
    IF(And('PH-data'[Former Active Operator]<>BLANK(),'PH-data'[Active operator]<>BLANK()),"Migratie",
    IF(And('PH-data'[Former Active Operator]<>BLANK(),'PH-data'[Active operator]=BLANK()),"Opzegging",
    IF(And('PH-data'[Former Active Operator]=BLANK(),'PH-data'[Active operator]<>BLANK()),
        SWITCH(
            TRUE(),
            StartDateActive <> "24-dec","Bestaande klant",
            StartDateActive = "24-dec","Klant")     
))))

[rajendraongole1 · Accepted solution]

Hi @RonaldvdH  - can you try below measure:

Aansluittype =
VAR StartDateActive = FORMAT('PH-data'[Patch start date], "YYYY-MM") -- Ensuring consistent format
VAR CurrentMonthYear = FORMAT(TODAY(), "YYYY-MM") -- Dynamically gets the current month and year

RETURN
IF(
'PH-data'[Fiber] = 1,
IF(
AND('PH-data'[Former Active Operator] <> BLANK(), 'PH-data'[Active operator] <> BLANK()),
"Migratie",
IF(
AND('PH-data'[Former Active Operator] <> BLANK(), 'PH-data'[Active operator] = BLANK()),
"Opzegging",
IF(
AND('PH-data'[Former Active Operator] = BLANK(), 'PH-data'[Active operator] <> BLANK()),
SWITCH(
TRUE(),
StartDateActive <> CurrentMonthYear, "Bestaande klant",
StartDateActive = CurrentMonthYear, "Klant"
)
)
)
)
)

Hope this works.

[rajendraongole1 · Accepted solution]

Hi @RonaldvdH  - can you try below measure:

Aansluittype =
VAR StartDateActive = FORMAT('PH-data'[Patch start date], "YYYY-MM") -- Ensuring consistent format
VAR CurrentMonthYear = FORMAT(TODAY(), "YYYY-MM") -- Dynamically gets the current month and year

RETURN
IF(
'PH-data'[Fiber] = 1,
IF(
AND('PH-data'[Former Active Operator] <> BLANK(), 'PH-data'[Active operator] <> BLANK()),
"Migratie",
IF(
AND('PH-data'[Former Active Operator] <> BLANK(), 'PH-data'[Active operator] = BLANK()),
"Opzegging",
IF(
AND('PH-data'[Former Active Operator] = BLANK(), 'PH-data'[Active operator] <> BLANK()),
SWITCH(
TRUE(),
StartDateActive <> CurrentMonthYear, "Bestaande klant",
StartDateActive = CurrentMonthYear, "Klant"
)
)
)
)
)

Hope this works.

[RonaldvdH]

@rajendraongole1 that did the trick thanks.

Just one more question about this part of the formula:

VAR CurrentMonthYear = FORMAT(TODAY(), "YYYY-MM")

How do i change this if I want to have the CurrentMonthYear -1 ?

I get exports from January 1st until January 31st but i receive those files in Februari so the currentmonth results in February instead of January 

[rajendraongole1 · Accepted solution]

Hi @RonaldvdH  - Good question, You can adjust CurrentMonthYear to dynamically get the previous month by using EOMONTH(TODAY(), -1). 

VAR CurrentMonthYear = FORMAT(EOMONTH(TODAY(), -1), "YYYY-MM")

THis helps.