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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rdwhite
Frequent Visitor

DAX formula to return a Boolean value for previous quarter in calculated column

 

Hi, I've been puzzling over this for some time.  I'd like to use a column called "Previous Quarter" in my Calendar.  I was able to back into current quarter, but I'm having a heck of a time with this one.  Can anyone help?

 

 

Calendar.PNG

1 ACCEPTED SOLUTION
MohammadLoran25
Super User
Super User

Hi @rdwhite ,

Follow Steps Below (Creating Calculated Columns) assuming my date table name is FullDDate:

 

1-Create Calculated Column:

 

YearQuarterNo =
CONVERT (
    CONCATENATE (
        YEAR ( FullDDate[Date] ),
        CONCATENATE ( "0", FullDDate[Quarter] )
    ),
    INTEGER
)

 
2-Then create calculated column below:
 
YearQuarterIndex =
RANKX (
    FullDDate,
    FullDDate[YearQuarterNo],
    FullDDate[YearQuarterNo],
    ASC,
    DENSE
)
 
AS your FInal step, create the calculated column below to get what you need:
 
PreviousQuarter =
IF (
    FullDDate[YearQuarterIndex]
        CALCULATE (
            MIN ( FullDDate[YearQuarterIndex] ) - 1,
            FILTER ( FullDDate, FullDDate[Date] = TODAY () )
        ),
    "YES",
    "NO"
)
 
If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.
 
Regards,
Loran

 

View solution in original post

9 REPLIES 9
rdwhite
Frequent Visitor

Here is what I've tried without sucess so far: "Previous Quarter Boolean = VAR PrevQuarter = DATEADD('Calendar'[Date],-1,QUARTER) RETURN IF(YEAR(PrevQuarter) = YEAR('Calendar'[Date]) && QUARTER(PrevQuarter) = QUARTER('Calendar'[Date]), TRUE(), FALSE() ) "  All returned False.  Also: "Previous Quarter Boolean = VAR PrevQuarterStartDate = DATE(YEAR('Calendar'[Date]), SWITCH(QUARTER('Calendar'[Date]), 1, 10, 2, 1, 3, 4, 4, 7), 1) VAR PrevQuarterEndDate = EOMONTH(PrevQuarterStartDate, 2) RETURN IF('Calendar'[Date] >= PrevQuarterStartDate && 'Calendar'[Date] <= PrevQuarterEndDate, TRUE(), FALSE() )"

Have you checked my answer? I think it is what you need.

 

The first step did not work...

Maybe your are missing fullddate[quarter]

 

This is a calculated column as well:

quarter=QUARTER(fullddate[date])

 

Then it is used as one of the inputs in step1.

@rdwhite 

You are doing sth wrong.

Are you creating a calculated column? What is the error?

Capture.jpeg

fullddate[quarter] in my formula is just number. 1,2,3,4 (without qtr string)

You can make it using a calculated column:

 

quarter=QUARTER(fullddate[date])

MohammadLoran25
Super User
Super User

Hi @rdwhite ,

Follow Steps Below (Creating Calculated Columns) assuming my date table name is FullDDate:

 

1-Create Calculated Column:

 

YearQuarterNo =
CONVERT (
    CONCATENATE (
        YEAR ( FullDDate[Date] ),
        CONCATENATE ( "0", FullDDate[Quarter] )
    ),
    INTEGER
)

 
2-Then create calculated column below:
 
YearQuarterIndex =
RANKX (
    FullDDate,
    FullDDate[YearQuarterNo],
    FullDDate[YearQuarterNo],
    ASC,
    DENSE
)
 
AS your FInal step, create the calculated column below to get what you need:
 
PreviousQuarter =
IF (
    FullDDate[YearQuarterIndex]
        CALCULATE (
            MIN ( FullDDate[YearQuarterIndex] ) - 1,
            FILTER ( FullDDate, FullDDate[Date] = TODAY () )
        ),
    "YES",
    "NO"
)
 
If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.
 
Regards,
Loran

 

Thanks for stepping through this with me.  I had even resulted to ChatGPT...

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.