topic Re: Last Fiscal quarter in DAX Commands and Tips

Last Fiscal quarter

Mohan_sc — Mon, 09 May 2022 09:08:02 GMT

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

Re: Last Fiscal quarter

amitchandak — Mon, 09 May 2022 09:41:01 GMT

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
https://www.youtube.com/watch?v=8-TlVx7P0A0

Re: Last Fiscal quarter

Anonymous — Thu, 12 May 2022 09:46:25 GMT

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 = ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ), "Qtr", QUARTER ( [Date] ), "Month", MONTH ( [Date] ) ) VAR _ADD_Fiscal = ADDCOLUMNS ( _Basic, "Fiscal Year", IF ( [Month] >= 4, [Year], [Year] - 1 ), "Fiscal Qtr", IF ( [Month] >= 4, [Qtr]-1, 4 ) ) RETURN _ADD_Fiscal

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.