Forum Discussion

RonaldvdH's avatar
RonaldvdH
Icon for Post Patron rankPost Patron
1 year ago
Solved

Need 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...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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's avatar
    rajendraongole1
    1 year ago

    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.

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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's avatar
RonaldvdH
Icon for Post Patron rankPost Patron
1 year ago

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's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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.