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.

mbudiman's avatar
mbudiman
Helper III
1 year ago

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's avatar
Anonymous
Not applicable
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.

  • mbudiman's avatar
    mbudiman
    Helper III
    1 year ago

    hi Yulia, the solution provided works. Thank you.