Forum Discussion

CDSouthall's avatar
CDSouthall
Regular Visitor
5 years ago
Solved

Reference Previous Quarter in SWITCH or Nested IF statement in DAX

I have a list of items in a table and want to be able to reference a date in the table and insert a text value to say if that date is "Completed in Current Quarter" or "Completed in Previous Quarter"...
  • amitchandak's avatar
    5 years ago

    CDSouthall , PreviousQuarter is going to return set of dates

    Try in

    #Completion_Title = SWITCH(TRUE(),
        AND([#Story_Close_Date]>STARTOFQUARTER(DevOps_All_User_Stories[#Current_Date].[Date]),[#Story_Close_Date]<>BLANK()),"Completed in Current Quarter",
        AND([#Story_Close_Date] in PREVIOUSQUARTER(DevOps_All_User_Stories[#Current_Date].[Date]),[#Story_Close_Date]<>BLANK()),"Completed in Previous Quarter"
    )

     

    or

    #Completion_Title = SWITCH(TRUE(),
        AND([#Story_Close_Date]>STARTOFQUARTER(DevOps_All_User_Stories[#Current_Date].[Date]),[#Story_Close_Date]<>BLANK()),"Completed in Current Quarter",
        AND([#Story_Close_Date]=minx(DevOps_All_User_Stories,PREVIOUSQUARTER(DevOps_All_User_Stories[#Current_Date].[Date])),[#Story_Close_Date]<>BLANK()),"Completed in Previous Quarter"
    )

     

    Ideally, you should use date table for time intelligence and should not use .date. Unless date has a timestamp.

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi



    Appreciate your Kudos.