Forum Discussion

Mohan_sc's avatar
Mohan_sc
Frequent Visitor
4 years ago
Solved

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
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.