please help in converting SQL query to Power Bi dax function - cal column

[Prathyusika]

 case when [ClosedDate] >= WM_START_DATE and

            [ClosedDate] <= DATEADD(day,90,WM_START_DATE) and GETDATE() >= WM_START_DATE then '90 Days'

          -- when[ClosedDate] >= [WM_START_DATE] and [ClosedDate] <= DATEADD(day,90,[WM_START_DATE]) and GETDATE() >= DATEADD(day,90,[WM_START_DATE]) then '90 Days'

            -- when [ClosedDate] >= prod_date and [ClosedDate] <= DATEADD(day,180,prod_date) and GETDATE() >= DATEADD----(day,180,prod_date) then '180 Days'

             when [ClosedDate] > dateadd(day,90 ,WM_START_DATE) and [ClosedDate] <= DATEADD(day,180,WM_START_DATE) and GETDATE() >= DATEADD(day,90,WM_START_DATE) then '180 Days'

             when [ClosedDate] > dateadd(day,180,WM_START_DATE) and [ClosedDate] <= DATEADD(day,270,WM_START_DATE) and GETDATE() >= DATEADD(day,180,prod_date) then '270 Days'    

             when [ClosedDate] > dateadd(day,270,WM_START_DATE) and [ClosedDate] <= DATEADD(day,360,WM_START_DATE) and GETDATE() >= DATEADD(day,270,WM_START_DATE) then '360 Days'

else 'Greater than 360 days'

             end as [Time Bucket]

[grazitti_sapna]

Hey, You can achieve this by 

Time Bucket =

VAR WM_START_DATE = 'YourTableName'[WM_START_DATE]

VAR ClosedDate = 'YourTableName'[ClosedDate]

VAR Today = TODAY()

RETURN

IF(

ClosedDate >= WM_START_DATE &&

ClosedDate <= DATEADD(WM_START_DATE, 90, DAY) &&

Today >= WM_START_DATE,

"90 Days",

IF(

ClosedDate > DATEADD(WM_START_DATE, 90, DAY) &&

ClosedDate <= DATEADD(WM_START_DATE, 180, DAY) &&

Today >= DATEADD(WM_START_DATE, 90, DAY),

"180 Days",

IF(

ClosedDate > DATEADD(WM_START_DATE, 180, DAY) &&

ClosedDate <= DATEADD(WM_START_DATE, 270, DAY) &&

Today >= DATEADD(WM_START_DATE, 180, DAY),

"270 Days",

IF(

ClosedDate > DATEADD(WM_START_DATE, 270, DAY) &&

ClosedDate <= DATEADD(WM_START_DATE, 360, DAY) &&

Today >= DATEADD(WM_START_DATE, 270, DAY),

"360 Days",

"Greater than 360 days"

)

)

)

)

Thank you

[AjithPrasath · Accepted solution]

Hi @Prathyusika ,

Please try the below code:

Time Bucket = 
    SWITCH(
        TRUE(),
        [ClosedDate] >= WM_START_DATE &&
        [ClosedDate] <= DATEADD(WM_START_DATE, 90, DAY) &&
        TODAY() >= WM_START_DATE, "90 Days",
        
        [ClosedDate] > DATEADD(WM_START_DATE, 90, DAY) &&
        [ClosedDate] <= DATEADD(WM_START_DATE, 180, DAY) &&
        TODAY() >= DATEADD(WM_START_DATE, 90, DAY), "180 Days",
        
        [ClosedDate] > DATEADD(WM_START_DATE, 180, DAY) &&
        [ClosedDate] <= DATEADD(WM_START_DATE, 270, DAY) &&
        TODAY() >= DATEADD(WM_START_DATE, 180, DAY), "270 Days",
        
        [ClosedDate] > DATEADD(WM_START_DATE, 270, DAY) &&
        [ClosedDate] <= DATEADD(WM_START_DATE, 360, DAY) &&
        TODAY() >= DATEADD(WM_START_DATE, 270, DAY), "360 Days",
        
        TRUE(), "Greater than 360 days"
    )

Best Regards,

Ajith Prasath

If this post helps, then please consider Accept it as the solution and give kudos to help the other members find it more quickly.