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

## Last Fiscal quarter

Hello, I have fiscal calender. I want to show data for last 1 quarter. Client fiscal quarter start for April.

I've currently  using

"Last QT Ticket = CALCULATE(COUNTA('in'[ticket_number]), DATEADD('in'[submit_my - Copy].[Date],-3,QUARTER))".

if any other calculation is ther let me know.

Regards,
Mohan
1 ACCEPTED SOLUTION
Community Support

Hi @Mohan_sc ,

I think you can try to create a DimDate table by calendar() or calendarauto() function, then use PREVIOUSQUARTER function.

``````DimDate =
VAR _Basic =
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Qtr", QUARTER ( [Date] ),
"Month", MONTH ( [Date] )
)
_Basic,
"Fiscal Year",
IF ( [Month] >= 4, [Year], [Year] - 1 ),
"Fiscal Qtr", IF ( [Month] >= 4, [Qtr]-1, 4 )
)
RETURN

If 'in'[submit_my - Copy] is a datetime format column, you can add a date only format calculated column by DATEVALUE function. Then create a relationship between 'in'[Date] and 'Dimdate'[Date].

``Last QT Ticket = CALCULATE(COUNTA('in'[ticket_number]), PREVIOUSQUARTER(DimDate[Date]))``

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Hi @Mohan_sc ,

I think you can try to create a DimDate table by calendar() or calendarauto() function, then use PREVIOUSQUARTER function.

``````DimDate =
VAR _Basic =
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Qtr", QUARTER ( [Date] ),
"Month", MONTH ( [Date] )
)
_Basic,
"Fiscal Year",
IF ( [Month] >= 4, [Year], [Year] - 1 ),
"Fiscal Qtr", IF ( [Month] >= 4, [Qtr]-1, 4 )
)
RETURN

If 'in'[submit_my - Copy] is a datetime format column, you can add a date only format calculated column by DATEVALUE function. Then create a relationship between 'in'[Date] and 'Dimdate'[Date].

``Last QT Ticket = CALCULATE(COUNTA('in'[ticket_number]), PREVIOUSQUARTER(DimDate[Date]))``

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@Mohan_sc , As long as Qtr start from Jan,Apr, JUL and Oct, These should work

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))

DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

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 - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors