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
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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