Reference Previous Quarter in SWITCH or Nested IF statement in DAX

[CDSouthall]

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

I have got the first part to work as the first AND statement works fine, but the 2nd and Statement that contains "=PREVIOUSQUARTER" errors.

If anyone could show me where i am going wrong that would be great.

#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]=PREVIOUSQUARTER(DevOps_All_User_Stories[#Current_Date].[Date]),[#Story_Close_Date]<>BLANK()),"Completed in Previous Quarter"
)

[amitchandak · Accepted solution]

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

[CDSouthall]

@amitchandak 

Many thanks indeed for your assistance on this, the first one worked great, so provided the answer i was looking for, many thanks again 🙂

--

#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"
)