Forum Discussion

mbudiman's avatar
mbudiman
Helper III
1 year ago
Solved

Drill-down using RELATED function

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 : ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

danextian's avatar
danextian
Super User
1 year ago

 

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.