Forum Discussion

WJ876400's avatar
WJ876400
Helper IV
3 years ago
Solved

Multiple If statement

Hi

 

I want to create the below table. I have created the date difference column but only want a number to appear under certain conditions. -

 

End dateStatusDate difference
01/12/2022Live43
01/10/2019Finished 
25/06/2019Pause 
01/12/2023Live 
02/05/2023Live 
10/10/2022Live95

 

I want the below to be blank -

 

Passed end date and pause = blank

Passed end date and finished = blank

Not passed end date = blank

 

Else show the date difference if Live and passed end date

 

hope that makes sense, any help is greatly appreciated

 

thanks

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Date diff CC =
VAR _today =
    TODAY ()
RETURN
    SWITCH (
        TRUE (),
        Data[End date] < _today
            && Data[Status] = "Pause", BLANK (),
        Data[End date] < _today
            && Data[Status] = "Finished", BLANK (),
        Data[End date] > _today, BLANK (),
        Data[End date] < _today
            && Data[Status] = "Live", DATEDIFF ( Data[End date], _today, DAY )
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Date diff CC =
VAR _today =
    TODAY ()
RETURN
    SWITCH (
        TRUE (),
        Data[End date] < _today
            && Data[Status] = "Pause", BLANK (),
        Data[End date] < _today
            && Data[Status] = "Finished", BLANK (),
        Data[End date] > _today, BLANK (),
        Data[End date] < _today
            && Data[Status] = "Live", DATEDIFF ( Data[End date], _today, DAY )
    )
  • negi007's avatar
    negi007
    Community Champion
    3 years ago

    WJ876400  you can create a calc column like below. 

     

    date_difference =
    var _todayDate = TODAY()
    var _endDate = 'Table'[End date]
    var _variance = if (_endDate < _todayDate && 'Table'[Status] = "Pause" ,"",
                    if (_endDate < _todayDate && 'Table'[Status] = "finished", "" ,
                         if (_endDate >_todayDate , "" ,
                             if (_endDate <_todayDate  && 'Table'[Status] = "live" , _todayDate-_endDate ,""))))
    return
    if (ISNUMBER(_variance),FORMAT(_variance,"General Number"),"")
     

    have attached pbix file as well.