topic Reference Previous Quarter in SWITCH or Nested IF statement in DAX in DAX Commands and Tips

Reference Previous Quarter in SWITCH or Nested IF statement in DAX

CDSouthall — Fri, 02 Oct 2020 08:35:19 GMT

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

Re: Reference Previous Quarter in SWITCH or Nested IF statement in DAX

amitchandak — Fri, 02 Oct 2020 10:05:16 GMT

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

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

Re: Reference Previous Quarter in SWITCH or Nested IF statement in DAX

CDSouthall — Mon, 05 Oct 2020 08:22:55 GMT

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