Current Quarter, Next Quarter, Next Quarter+1 based on selected Fiscal Quarter

[yhv]

Hi

Is there a way to create measures for revenue as Current Quarter, Next Quarter, Next Quarter+1 based on selected Fiscal Quarter.

FYQ	CQ(Current Quarter)	NQ(Next Quarter)	NQ+1(Next Quarter+1)
2022-Q2	Revenue for 2022-Q2	Revenue for 2022-Q3	Revenue for 2022-Q4

[v-kaiyue-msft · Accepted solution]

Hi @yhv ,

Modify measure.

cq = 
CALCULATE (
    SUM ( financials[ Sales] ),
    FILTER (
        ALL ( financials ),
        'financials'[Quarter] = MAX ( 'Table'[Quarter] )
    )
)

nq = 
VAR _qtr_no_r =
    RIGHT ( SELECTEDVALUE ( 'Table'[Quarter] ), 1 )
VAR _qtr_no_l =
    LEFT ( SELECTEDVALUE ( 'Table'[Quarter] ), 6 )
VAR _qtr1 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        FILTER ( ALL ( financials ), 'financials'[Quarter] = _qtr_no_l & _qtr_no_r + 1 )
    )
VAR _qtr2 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        FILTER (
            ALL ( financials ),
            RIGHT ( 'financials'[Quarter], 1 ) = "1"
                && VALUE ( LEFT ( 'financials'[Quarter], 4 ) )
                    = VALUE ( LEFT ( MAX ( 'Table'[Quarter] ), 4 ) ) + 1
        )
    )
RETURN
    IF ( _qtr_no_r = "4", _qtr2, _qtr1 )

nq + 1 = 
VAR _qtr_no_r =
    RIGHT ( SELECTEDVALUE ( 'Table'[Quarter] ), 1 )
VAR _qtr_no_l =
    LEFT ( SELECTEDVALUE ( 'Table'[Quarter] ), 6 )
VAR _qtr1 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        FILTER ( ALL ( financials ), 'financials'[Quarter] = _qtr_no_l & _qtr_no_r + 2 )
    )
VAR _qtr2 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        FILTER (
            ALL ( financials ),
            RIGHT ( 'financials'[Quarter], 1 ) = "1"
                && VALUE ( LEFT ( 'financials'[Quarter], 4 ) )
                    = VALUE ( LEFT ( MAX ( 'Table'[Quarter] ), 4 ) ) + 1
        )
    )
VAR _qtr3 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        FILTER (
            ALL ( financials ),
            RIGHT ( 'financials'[Quarter], 1 ) = "2"
                && VALUE ( LEFT ( 'financials'[Quarter], 4 ) )
                    = VALUE ( LEFT ( MAX ( 'Table'[Quarter] ), 4 ) ) + 1
        )
    )
RETURN
    IF ( _qtr_no_r = "3", _qtr2, IF ( _qtr_no_r = "4", _qtr3, _qtr1 ) )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[yhv]

Hi Clara Gong

This logic is working perfectly fine. In this logic, I need to add one more filter as catefory from Category table along with fiscal Quarter. 
I added that field using cross join and is working fine. However it is taking lot of time to load the data for any selected fiscal quarter.
It would be very helpful if u assist me where I am doing wrong. Below is the logic I updated with

nq = 

VAR _qtr_no_r =

    RIGHT ( SELECTEDVALUE ( 'Table'[Quarter] ), 1 )

VAR _qtr_no_l =

    LEFT ( SELECTEDVALUE ( 'Table'[Quarter] ), 6 )

VAR _Category=SELECTEDVALUE('Category'[Category Name])

VAR _qtr1 =

    CALCULATE (

        SUM ( financials[ Sales] ),

        FILTER ( 

CROSSJOIN(

ALL(financials)

ALL(Category)

),

'financials'[Quarter] = _qtr_no_l & _qtr_no_r + 1 || 'Category'[Category Name]=_Category

)

    )

VAR _qtr2 =

    CALCULATE (

        SUM ( financials[ Sales] ),

        FILTER (

CROSSJOIN(

ALL ( financials ),

ALL(Category)

),

RIGHT ( 'financials'[Quarter], 1 ) = "1"

&& VALUE ( LEFT ( 'financials'[Quarter], 4 ) )

= VALUE ( LEFT ( MAX ( 'Table'[Quarter] ), 4 ) ) + 1 || 'Category'[Category Name]=_Category

)

        )

    )

RETURN

    IF ( _qtr_no_r = "4", _qtr2, _qtr1 )

[v-kaiyue-msft]

Hi @yhv ,

1.Create calculate table.

Table =

DISTINCT('financials'[Date])

2.Create measure.

cq =

CALCULATE (

    SUM ( financials[ Sales] ),

    FILTER (

        ALL ( financials ),

        'financials'[Date].[Quarter] = MAX ( 'Table'[Date].[Quarter] )

            && 'financials'[Date].[Year] = MAX ( 'Table'[Date].[Year] )

    )

)

nq =

VAR _qtr_no =

    SELECTEDVALUE ( 'Table'[Date].[QuarterNo] )

VAR _qtr1 =

    CALCULATE (

        SUM ( financials[ Sales] ),

        FILTER (

            ALL ( financials ),

            'financials'[Date].[QuarterNo]

                = MAX ( 'Table'[Date].[QuarterNo] ) + 1

                && 'financials'[Date].[Year] = MAX ( 'Table'[Date].[Year] )

        )

    )

VAR _qtr2 =

    CALCULATE (

        SUM ( financials[ Sales] ),

        FILTER (

            ALL ( financials ),

            'financials'[Date].[QuarterNo] = 1

                && 'financials'[Date].[Year]

                    = MAX ( 'Table'[Date].[Year] ) + 1

        )

    )

RETURN

    IF ( _qtr_no = 4, _qtr2, _qtr1 )

nq + 1 =

VAR _qtr_no =

    SELECTEDVALUE ( 'Table'[Date].[QuarterNo] )

VAR _qtr1 =

    CALCULATE (

        SUM ( financials[ Sales] ),

        FILTER (

            ALL ( financials ),

            'financials'[Date].[QuarterNo]

                = MAX ( 'Table'[Date].[QuarterNo] ) + 2

                && 'financials'[Date].[Year] = MAX ( 'Table'[Date].[Year] )

        )

    )

VAR _qtr2 =

    CALCULATE (

        SUM ( financials[ Sales] ),

        FILTER (

            ALL ( financials ),

            'financials'[Date].[QuarterNo] = 1

                && 'financials'[Date].[Year]

                    = MAX ( 'Table'[Date].[Year] ) + 1

        )

    )

VAR _qtr3 =

    CALCULATE (

        SUM ( financials[ Sales] ),

        FILTER (

            ALL ( financials ),

            'financials'[Date].[QuarterNo] = 2

                && 'financials'[Date].[Year]

                    = MAX ( 'Table'[Date].[Year] ) + 1

        )

    )

RETURN

    IF ( _qtr_no = 3, _qtr2, IF ( _qtr_no = 4, _qtr3, _qtr1 ) )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[yhv]

Hi Clara Gong

Thanks for the reply. The logic mentioned here is applied for normal date or Quarter.

However I need the same logic for  fiscal quarter where
Nov,Dec,Jan is Q1,
Feb,Mar,Apr is Q2,

May,Jun,Jul is Q3 and

Aug, Sep,Oct is Q4.

I already have fiscal quarter in my date table and fact table. Based on this I am able to to create current quarter. 
The format of fiscal quarter is 2014-Q3,2014-Q4... which is in text format.

I am stuck in creating Next Quarter as I have to add logic as Current Quarter +1. I am getting error "Cannot Convert Value 2014-Q3 of type text to type number"

Please assist me for getting the required solution.