assign rank to only completed Quarters in FY

[YG78574]

I have a monthly data, i need to calculate QoQ, the logic for it is (latest completed quarter- previous completed quarter/previous completed quarter), now for this want to create a new column which assigns rank to only fully completed quarters. Our FY is April to March, the last completed quarter is oct, nov, dec for me now.  i also have jan data but the jan data should not get included in this as the quarter is not yet completed. can anyone guide me in it to make it dynamic as it only gives rank to fully completed quarters. 

[Anonymous]

Hi @YG78574

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a measure to determine completed quarters. This column returns TRUE for the date when the quarter was fully completed.

Is Quarter Complete = 
var CurrentQuarter = QUARTER(SELECTEDVALUE('Table'[Date]))
var CurrentYear = YEAR(SELECTEDVALUE('Table'[Date]))
RETURN 
IF(
    CALCULATE(COUNTROWS('Table'), 
        FILTER(
            ALL('Table'), 
            QUARTER('Table'[Date]) = CurrentQuarter 
            && 
            YEAR('Table'[Date]) = CurrentYear
            )
        ) = 3, 
        TRUE,
        FALSE
)
        

Create measures. Calculate the latest completed quarter amount.

CurrentQuarterSales = 
VAR max_date = 
    CALCULATE(
        MAX('Table'[Date]), 
        FILTER(
            ALL('Table'), 
            'Table'[Is Quarter Complete] = TRUE()
        )
    )
RETURN 
CALCULATE(
    SUM('Table'[Values]), 
    FILTER(
        ALL('Table'), 
        QUARTER('Table'[Date]) = QUARTER(max_date)
        )
    )

Calculate the amount of the previous completed quarter.

PreviousQuarterSales = 
VAR max_date = 
    CALCULATE(
        MAX('Table'[Date]), 
        FILTER(
            ALL('Table'), 
            'Table'[Is Quarter Complete] = TRUE()
        )
    )
RETURN 
CALCULATE(
    SUM('Table'[Values]), 
    FILTER(
        ALL('Table'), 
        QUARTER('Table'[Date]) = QUARTER(max_date) - 1
        )
    )

The final result is as follows:

QoQ Growth = 
VAR max_date = 
    CALCULATE(
        MAX('Table'[Date]), 
        FILTER(
            ALL('Table'), 
            'Table'[Is Quarter Complete] = TRUE()
        )
    )
VAR CurrentQuarterSale = 
    CALCULATE(
        SUM('Table'[Values]), 
        FILTER(
            ALL('Table'), 
            QUARTER('Table'[Date]) = QUARTER(max_date)
        )
    )

VAR PreviousQuarterSales = 
    CALCULATE(
        SUM('Table'[Values]), 
        FILTER(
            ALL('Table'), 
            QUARTER('Table'[Date]) = QUARTER(max_date) - 1
        )
    )

RETURN DIVIDE(CurrentQuarterSale - PreviousQuarterSales , PreviousQuarterSales)

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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

[YG78574]

[Is QuarterComplete] is not working correctly, it is giving false to completed quaters too.

[Anonymous]

Hi @YG78574 

It's best to provide the pbix file and be careful to remove sensitive data.

Regards,

Nono Chen