Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX query for a Case statement

Hi , I am new in PowerBI, Can somebody please help me to create a DAX for the following Case statement,

 

Thanks,

 

Case when Year(SOLINE.[Deliv Date]) < Year(getdate()) then 'CurrentPeriod'
when Year(SOLINE.[Deliv Date]) = Year(getdate()) and month(SOLINE.[Deliv Date]) <= month(getdate()) then 'CurrentPeriod'
when Year(SOLINE.[Deliv Date]) = Year(getdate()) and month(SOLINE.[Deliv Date]) = month(getdate()) +1 then 'CurrentPeriod_1'
when Year(SOLINE.[Deliv Date]) = Year(getdate()) and month(SOLINE.[Deliv Date]) = month(getdate()) +2 then 'CurrentPeriod_2'
when Year(SOLINE.[Deliv Date]) = Year(getdate()) and month(SOLINE.[Deliv Date]) = month(getdate()) +3 then 'CurrentPeriod_3'
when Year(SOLINE.[Deliv Date]) = Year(getdate()) and month(SOLINE.[Deliv Date]) > month(getdate()) +3 then 'Forward' End SODeliveryPeriod

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Getdate() seems to be the function in SQL, we will use Now() to get current datetime in DAX.

    In addition to  lbendlin 's reply, you can try this code to create a calculated column.

     

    Period =
SWITCH (
    TRUE (),
    YEAR ( SOLINE.[Deliv Date] ) < YEAR ( NOW () ), 'CurrentPeriod',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] ) <= MONTH ( NOW () ), 'CurrentPeriod',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] )
            = MONTH ( NOW () ) + 1, 'CurrentPeriod_1',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] )
            = MONTH ( NOW () ) + 2, 'CurrentPeriod_2',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] )
            = MONTH ( NOW () ) + 3, 'CurrentPeriod_3',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] )
            > MONTH ( NOW () ) + 3, 'Forward',
    "SODeliveryPeriod "
)

     

     

    Best Regards,
    Rico Zhou

     

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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Getdate() seems to be the function in SQL, we will use Now() to get current datetime in DAX.

    In addition to  lbendlin 's reply, you can try this code to create a calculated column.

     

    Period =
SWITCH (
    TRUE (),
    YEAR ( SOLINE.[Deliv Date] ) < YEAR ( NOW () ), 'CurrentPeriod',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] ) <= MONTH ( NOW () ), 'CurrentPeriod',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] )
            = MONTH ( NOW () ) + 1, 'CurrentPeriod_1',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] )
            = MONTH ( NOW () ) + 2, 'CurrentPeriod_2',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] )
            = MONTH ( NOW () ) + 3, 'CurrentPeriod_3',
    YEAR ( SOLINE.[Deliv Date] ) = YEAR ( NOW () )
        && MONTH ( SOLINE.[Deliv Date] )
            > MONTH ( NOW () ) + 3, 'Forward',
    "SODeliveryPeriod "
)

     

     

    Best Regards,
    Rico Zhou

     

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