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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Caitlin_Knox
Advocate III
Advocate III

Calculating current Quarter

I'm trying to create a calculated column to return the current quarter like I do for current month. I use these mostly as a visualization filter so the report updates to the current information.

This is the formula I use for Current Month:

CurrentMonth = IF(YEAR('LST Report'[EffectiveDate])&MONTH('LST Report'[EffectiveDate])=YEAR(NOW())&MONTH(NOW()),1,0)

This is what I'm trying for Current Quarter, it is not working as I would expect.

CurrentQuarter = IF(YEAR('LST Report'[EffectiveDate])&MONTH('LST Report'[EffectiveDate]+2/3)=YEAR(NOW())&MONTH(NOW()+2/3),1,0)

 Any thoughts are appreciated

 

1 ACCEPTED SOLUTION
Flori_Abb
Helper I
Helper I

Hi Caitlin_Knox

 

i guess you enter this formula in a date table and you want to add a column what shows you 1 if the date is the current quater. In this case i added before a quaternumber column:

 

QuaterNumber = if(dim_Date[Year] = year(now());roundup(dim_Date[MonthNumberOfYear]/3;0);0) and then

 

Current QTD = if(dim_Date[QuaterNumber] = roundup(month(now()-1)/3;0);1;0)

That works for me fine, maybe it helps you also.

KR

Florian

View solution in original post

3 REPLIES 3
SBoraste
Regular Visitor

You can calculate the current Quarter Sales using below DAX:

CY Quarter = CALCULATE(SUM('Sample Data'[Total Budget]),YEAR('Sample Data'[Project Start Date])=YEAR(TODAY()),
'Date Table'[QuarterOfYear]=ROUNDUP(MONTH(TODAY()) / 3, 0))
v-sihou-msft
Employee
Employee

@Caitlin_Knox

 

You can also use following expression to get the Quarter number.

 

Quarter Number = INT(FORMAT(NOW(),"q"))

Regards,

 

Flori_Abb
Helper I
Helper I

Hi Caitlin_Knox

 

i guess you enter this formula in a date table and you want to add a column what shows you 1 if the date is the current quater. In this case i added before a quaternumber column:

 

QuaterNumber = if(dim_Date[Year] = year(now());roundup(dim_Date[MonthNumberOfYear]/3;0);0) and then

 

Current QTD = if(dim_Date[QuaterNumber] = roundup(month(now()-1)/3;0);1;0)

That works for me fine, maybe it helps you also.

KR

Florian

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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