Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
6 years ago
Solved

DAX help

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"

 

 

Need Help
Show and Tell
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
    tkirilov
    Resolver I
    6 years ago

    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", ""))))

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,