Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mbudiman
Frequent Visitor

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 : "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 tableRevenue tableFiscal Qtr tableFiscal Qtr tableRevenue change by Fiscal Qtr is correctRevenue change by Fiscal Qtr is correctRevenue change by Fiscal Qtr and Sales Region is wrongRevenue change by Fiscal Qtr and Sales Region is wrong

1 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:

vxuxinyimsft_0-1729589820889.png

 

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.

View solution in original post

4 REPLIES 4
mbudiman
Frequent Visitor

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
    )
)

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:

vxuxinyimsft_0-1729589820889.png

 

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.

hi Yulia, the solution provided works. Thank you.

danextian
Super User
Super User

 

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.