Search code examples
powerbidaxpowerbi-desktopdaxstudio

Calculated column with an if statement in DAX

I would like to create a column which will includes Current_Week, Previous_Week,and Non

get_CW = //works fine
IF(
    AND(
    WEEKNUM(TODAY()) = WEEKNUM('Calendar'[Date]),
    YEAR(TODAY()) = YEAR('Calendar'[Date])
   ),
  "CW",
   "Non"
)

I am stuck to calculate PW which is below

get_CW_and_PW = 

IF(    
    AND(
    WEEKNUM(TODAY()) = WEEKNUM('Calendar'[Date]),
    YEAR(TODAY()) = YEAR('Calendar'[Date])
   ),
   "CW",
 IF(    
  /// CW-7 days
   ),
   "PW",

   "Non"
)
Solution

  • Use variables:

    get_CW =
VAR Current_Week = WEEKNUM ( TODAY () )
VAR Previous_Week = WEEKNUM ( TODAY () - 7 )
VAR Result =
    SWITCH (
        WEEKNUM ( 'Calendar'[Date] ),
        Current_Week, "CW",
        Previous_Week, "PW",
        "None"
    )
RETURN Result