Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
CDSouthall
Regular Visitor

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

 

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

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors