SQL Case to DAX statement

[Anonymous]

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

[v-juanli-msft]

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.

[v-juanli-msft]

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 · Accepted solution]

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.