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 ...
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.

     

     

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.