Drill-down using RELATED function

[mbudiman]

hello,

I have 2 tables, Fiscal_Qtr table and Sales tables. Fiscal_Qtr table contains custom financial quarter period, where a field called Qtr_Sequence_No is used to define sequence of Quarter : "0" means current quarter, -1 means last quarter, -2 means last 2 quarter, etc

To calculate revenue change for previous Quater, I created a measure using RELATED function for 'Previous Qtr Revenue' and 'Revenue Change'. Calculation for 'Previous Qtr Revenue' works correctly to show revenue for previous full Quarter, however, when break down by Sales Region, Previous Qtr Revenue does not automatically return respective Sales Region previous quarter revenue, it return previous full Quarter revenue instead. For example : Previous Qtr Revenue for FY24Q3 for America should be $250k, but it shows $825k.

Question : how to create a Measure that return previous quarter revenue that automatically change based on Dimension breakdown (e.g Sales Region) ?

Revenue tableFiscal Qtr tableRevenue change by Fiscal Qtr is correctRevenue change by Fiscal Qtr and Sales Region is wrong

[mbudiman]

I forget to mention that the tables are joined using "Fiscal_Qtr" column.

DAX for "Previous Qtr Revenue" measure is :

Prev_Qtr_Revenue =

CALCULATE(

    SUM(Sales[Revenue]),

    FILTER(

        ALL(Sales),

        RELATED(Fiscal_Qtr_Table[Qtr_Sequence_No]) = MAX(Fiscal_Qtr_Table[Qtr_Sequence_No]) - 1

    )

)

[Anonymous · Accepted solution]

Hi @mbudiman 

Please try the following formula.

Prev_Qtr_Revenue = 
CALCULATE(
    SUM(Sales[Revenue]),
    FILTER(
        ALLEXCEPT(Sales, Sales[Sale Region]),
        RELATED(Fiscal_Qtr_Table[Qtr_Sequence_No]) = MAX(Fiscal_Qtr_Table[Qtr_Sequence_No]) - 1
    )
)

In my test, I used the Fiscal_Qtr column of Fiscal_Qtr_Table for the matrix rows, and the Columns and Values ​​were from the Sales table.

Output:

Best Regards,
Yulia Xu

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

[mbudiman]

hi Yulia, the solution provided works. Thank you.

[danextian]

Hi @mbudiman 

I am wondering how you are  using RELATED in your use case but I am assuming whatever your formula is, it is pulling the previous quarter's value as a whole.  Try the following measure:

Previous Quarter's Rev = 
CALCULATE (
    [sales],
    FILTER (
        ALL ( Fiscal_Qtr ),
        Fiscal_Qtr[Qtr_Sequence_No]
            = MAX ( Fiscal_Qtr[Qtr_Sequence_No] ) - 1
    )
)

There should be a  relationship between quarter dim and the fact tables. Use quarter column from the quarter table.