Forum Discussion
Post PatronNeed help with my Formula
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")
))))
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 yearRETURN
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.
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.
3 Replies
- rajendraongole1
Super User
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 yearRETURN
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.
RonaldvdHrajendraongole1 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
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.