Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago
Solved

Are Variables possible in my code?

Hi,

 

I'm struggling to understand how the attached code works, is it possible to modify it with Variables to make it read easier?

 

Case Length (Adj) =
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        NOW (),
        DAY
    ),
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        'Cases'[Resolution Date],
        DAY
    )
)

Thanks

Need Help
Show and Tell
  • ERD's avatar
    ERD
    3 years ago

    Hi ArchStanton ,

    You can try to rewrite it like this:

    Case Length (Adj) =
VAR date_from =
    IF (
        ISBLANK ( 'Cases'[legacycasecreationdate] ),
        'Cases'[Created On],
        'Cases'[legacycasecreationdate]
    )
RETURN
    IF (
        'Cases'[statecode] = "Active",
        DATEDIFF ( date_from, NOW (), DAY ),
        DATEDIFF ( date_from, 'Cases'[Resolution Date], DAY )
    )

3 Replies

  • ERD's avatar
    ERD
    Community Champion
    3 years ago

    Hi ArchStanton ,

    You can try to rewrite it like this:

    Case Length (Adj) =
VAR date_from =
    IF (
        ISBLANK ( 'Cases'[legacycasecreationdate] ),
        'Cases'[Created On],
        'Cases'[legacycasecreationdate]
    )
RETURN
    IF (
        'Cases'[statecode] = "Active",
        DATEDIFF ( date_from, NOW (), DAY ),
        DATEDIFF ( date_from, 'Cases'[Resolution Date], DAY )
    )
  • ERD's avatar
    ERD
    Community Champion
    3 years ago

    ArchStanton , your code says:

    If legacycasecreationdate is empty, consider Created On as a Date From. If it's not empty, use it as a Date From.

    Then wherever statecode = "Active", count how many days between the date chosen above and now. If statecode has some other value, count how many days between the date chosen above and a Resolution Date.