DAX help

[vjnvinod]

i have some list of opportunities which has Anticipated Win date, see below

i want to create a coloumn which subtracts (Anticipated date-Current date)[Note: current date dax should be used]

and gives me the counts of the date

after giving the count of the days

it should calcuate like below

if the count is <0, show that to me as  "Lapsed"

If the Count is<=30, show that as "0-30" & for <=60, show that as 30-60,

 and for <=90,"60-90"  and for >90,"More than 90"

[v-lid-msft]

Hi @vjnvinod ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @vjnvinod ,

We can create two measures in this table visual to meet your requirement:

Date Counts =
DATEDIFF ( TODAY (), MIN ( 'Table'[AnticipatedWinDate] ), DAY )

Status =
IF (
    [Date Counts] < 0,
    "Lapsed",
    IF (
        [Date Counts] <= 30,
        "0-30",
        IF (
            [Date Counts] <= 60,
            "30-60",
            IF ( [Date Counts] <= 90, "60-90", "More than 90" )
        )
    )
)

Best regards,

[tkirilov]

Hi @vjnvinod ,

I would probably tackle this in two steps, just because it'd be easier to debug:

1. Create a calculated column, which counts the days since start date, like:

DayCount = Dateiff([AnticipatedWinDate], Today(), Day)

2. Create a conditional column using the logic you mention below. 

However, if you'd rather create a measure which calculates all of that at once, it would probably look something like this: 

If(Dateiff([AnticipatedWinDate], Today(), Day) < 0, "Lapsed", if(Dateiff([AnticipatedWinDate], Today(), Day) <= 30, "0-30", if(Dateiff([AnticipatedWinDate], Today(), Day) <= 60, "30-60", if(Dateiff([AnticipatedWinDate], Today(), Day)<= 90, "60-90", (if(Dateiff([AnticipatedWinDate], Today(), Day) > 90,"More than 90", ""))))