Forum Discussion
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".
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"
)
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.
2 Replies
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.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" )
