Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SQL Case to DAX statement

Can anyone please help me in writing the DAX statement for following SQL casees asap

 

1. case when fin_pymt_run<getdate() and ZLSPR='' then 'v1'

               when fin_pymt_run=pymt_run_td  and ZLSPR='' then 'v2'

               when (fin_pymt_run<getdate() or fin_pymt_run=pymt_run_td) and ZLSPR='R' then 'v3'  end

 

2. DATEADD(dd, CASE WHEN DATEDIFF(dd,0,b.pymt_run)%7 > 4 THEN 7-DATEDIFF(dd,0,b.pymt_run)%7 ELSE 0 END,b.pymt_run) as fin_pymt_run

 

 

3. case

       when year(getdate())=year(due_date) then datepart(wk,cast (a.due_date as date))-datepart(wk,getdate())

       when year(due_date)>year(getdate()) then datepart(wk,cast (a.due_date as date))+ (datepart(wk,DATEFROMPARTS(year(getdate()),12,31))-datepart(wk,getdate())) -1

       else  -999 end as wk

 

4. case

       when datepart(dd,a.due_date)<=10 then DATEFROMPARTS(datepart(yy,a.due_date),datepart(mm,a.due_date),10)

       when datepart(dd,a.due_date)>10 and datepart(dd,a.due_date)<=15 then DATEFROMPARTS(datepart(yy,a.due_date),datepart(mm,a.due_date),15)

       when datepart(dd,a.due_date)>15 and datepart(dd,a.due_date)<=20 then DATEFROMPARTS(datepart(yy,a.due_date),datepart(mm,a.due_date),20)

       else EOMONTH(a.due_date)  

      end as pymt_run,

 

5. case

       when datepart(dd,getdate())<=10 then DATEFROMPARTS(datepart(yy,getdate()),datepart(mm,getdate()),10)

       when datepart(dd,getdate())>10 and datepart(dd,getdate())<=15 then DATEFROMPARTS(datepart(yy,getdate()),datepart(mm,getdate()),15)

       when datepart(dd,getdate())>15 and datepart(dd,getdate())<=20 then DATEFROMPARTS(datepart(yy,getdate()),datepart(mm,getdate()),20)

       else EOMONTH(getdate())  

      end as pymt_run_td

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    If [fin_pymt_run], [ pymt_run_td],  [b.pymt_run], [due_date]  are columns in your table, then you can create calculated columns in Power BI as below:

    Column1 =
SWITCH (
    TRUE (),
    [fin_pymt_run] < TODAY ()
        && ZLSPR = BLANK (), "v1",
    [fin_pymt_run] = [ pymt_run_td]
        && ZLSPR = BLANK (), "v2",
    (
        [fin_pymt_run] < TODAY ()
            || [fin_pymt_run] = [ pymt_run_td]
    )
        && ZLSPR = "R", "v3"
)



Column2 =
VAR number1 =
    IF (
        MOD ( DATEDIFF ( DATE ( 1900, 1, 1 ), [b.pymt_run], DAY ), 7 ) > 4,
        7 - MOD ( DATEDIFF ( DATE ( 1900, 1, 1 ), [b.pymt_run], DAY ), 7 ),
        0
    )
RETURN
DATEADD ( [b.pymt_run], number1, DAY )



Column3 =
SWITCH (
    TRUE (),
YEAR ( TODAY () ) = YEAR ( [due_date] ), 
WEEKNUM ( DATEVALUE ( [due_date] ) ) - WEEKNUM ( TODAY () ),
YEAR ( [due_date] ) > YEAR ( TODAY () ), 
WEEKNUM ( DATEVALUE ( [due_date] ) )
        + ( WEEKNUM ( DATE ( YEAR ( TODAY () ), 12, 31 ) ) - WEEKNUM ( TODAY () ) ) - 1,
    -999
)

    If these are correct on your side, feel free to tell me and if you need help with the remaining parts( statement4, 5).

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

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

    Hi Anonymous 

    If [fin_pymt_run], [ pymt_run_td],  [b.pymt_run], [due_date]  are columns in your table, then you can create calculated columns in Power BI as below:

    Column1 =
SWITCH (
    TRUE (),
    [fin_pymt_run] < TODAY ()
        && ZLSPR = BLANK (), "v1",
    [fin_pymt_run] = [ pymt_run_td]
        && ZLSPR = BLANK (), "v2",
    (
        [fin_pymt_run] < TODAY ()
            || [fin_pymt_run] = [ pymt_run_td]
    )
        && ZLSPR = "R", "v3"
)



Column2 =
VAR number1 =
    IF (
        MOD ( DATEDIFF ( DATE ( 1900, 1, 1 ), [b.pymt_run], DAY ), 7 ) > 4,
        7 - MOD ( DATEDIFF ( DATE ( 1900, 1, 1 ), [b.pymt_run], DAY ), 7 ),
        0
    )
RETURN
DATEADD ( [b.pymt_run], number1, DAY )



Column3 =
SWITCH (
    TRUE (),
YEAR ( TODAY () ) = YEAR ( [due_date] ), 
WEEKNUM ( DATEVALUE ( [due_date] ) ) - WEEKNUM ( TODAY () ),
YEAR ( [due_date] ) > YEAR ( TODAY () ), 
WEEKNUM ( DATEVALUE ( [due_date] ) )
        + ( WEEKNUM ( DATE ( YEAR ( TODAY () ), 12, 31 ) ) - WEEKNUM ( TODAY () ) ) - 1,
    -999
)

    If these are correct on your side, feel free to tell me and if you need help with the remaining parts( statement4, 5).

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

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

    Hi Anonymous 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case?
    If not, please let me know.
     
    Best Regards
    Maggie
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    The rest two columns

    Column4 =
SWITCH (
    TRUE (),
    DAY ( [due_date] ) <= 10, DATE ( YEAR ( [due_date] ), MONTH ( [due_date] ), 10 ),
    DAY ( [due_date] ) > 10
        && DAY ( [due_date] ) <= 15, DATE ( YEAR ( [due_date] ), MONTH ( [due_date] ), 15 ),
    DAY ( [due_date] ) > 15
        && DAY ( [due_date] ) <= 20, DATE ( YEAR ( [due_date] ), MONTH ( [due_date] ), 20 ),
    EOMONTH ( [due_date], 0 )
)



Column5 =
SWITCH (
    TRUE (),
    DAY ( TODAY () ) <= 10, DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 10 ),
    DAY ( TODAY () ) > 10
        && DAY ( TODAY () ) <= 15, DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 15 ),
    DAY ( TODAY () ) > 15
        && DAY ( TODAY () ) <= 20, DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 20 ),
    EOMONTH ( TODAY (), 0 )
)
    If you have any problem, please feel free to let me know.
     
     
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.