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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

Top Solution Authors