cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

1 ACCEPTED SOLUTION
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

9 REPLIES 9
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() )"

Super User

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

Frequent Visitor

The first step did not work...

Super User

This is a calculated column as well:

quarter=QUARTER(fullddate[date])

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

Super User

You are doing sth wrong.

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

Frequent Visitor

Super User

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

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

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors