Forum Discussion

Prathyusika's avatar
Prathyusika
Frequent Visitor
3 years ago
Solved

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

 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]
Need Help
  • AjithPrasath's avatar
    AjithPrasath
    3 years ago

    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.

2 Replies

  • AjithPrasath's avatar
    AjithPrasath
    Resolver II
    3 years ago

    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.

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    3 years ago

    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