Conditional Column

[Anonymous]

Hi Folks,

I am working on creating a conditional column like the below:

Condition	Values in new column
contract to expire in 15 days from the current date	- 15 days
contract to expire in 30 days from the current date	- 30 days
contract to expire in 60 days from the current date	- 60 days
contract to expire in 90 days from the current date	- 90 days

I want the new column to be used as a filter/slicer in my report. My desired slicer outcome is

• if I select 60 days, all contracts expiring from 'today to 60 days in future' should show up.
• if I select 90 days, all contracts from 'today to 90 days' should show up.

But what I get is:

• if I select 90 days, I get only contracts expiring between 60 and 90 days

 Can anyone please help me here?

The logic used by me- if [expiry days]>0 and [expiry days]<60 then 60 days
                              if [expiry days]>0 and [expiry days]<90 then 90 days

[Anonymous]

Hello @Anonymous ,

try using with:    if [expiry days]>0 and [expiry days]<90 then 90 days

                              if [expiry days]>0 and [expiry days]<60 then 60 days

[Anonymous]

Thanks but this does not help

[Anonymous · Accepted solution]

Hello @Anonymous ,

You can achieve the desired result by separately creating column with each of this condition.

Days = DATEDIFF(TODAY(),'Date'[Date],DAY)

15 days = if('Date'[Days]<15,"15 days")

30 days = if('Date'[Days]<30,"30 days")

60 days = if('Date'[Days]<60,"60 days")

90 days = if('Date'[Days]<90,"90 days")

then combine values of all the column in one column

bridge = COMBINEVALUES("|",'Date'[15 days],'Date'[30 days],'Date'[60 days],'Date'[90 days])

create new table like below,

Slicer =

DISTINCT (

UNION (

SELECTCOLUMNS (

'Date',

"Selection", 'Date'[15 days],

"bridge", 'Date'[bridge]

),

SELECTCOLUMNS (

'Date',

"Selection",'Date'[30 days],

"bridge", 'Date'[bridge]

),

SELECTCOLUMNS ('Date',

"selection",'Date'[60 days],

"bridge",'Date'[bridge]

),

SELECTCOLUMNS ('Date',

"Selection",'Date'[90 days],

"bridge",'Date'[bridge]

)))

join the twe table by bridge column and use selection column in slicer.

Please find my attached pbix file here pbix file