Last Fiscal quarter

[Mohan_sc]

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

[Anonymous · Accepted solution]

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.

[amitchandak]

@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