cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Meena Member
Member

Need help in referring to a value on different tab of the same report

Hello,

I have developed a Fortnightly salary report that has 2 tabs (tab1 & tab2). They have common underlying db tables:

Emp_details and Emp_Costing

Relationship between these tables is Many to one from Emp_Costing to Emp_details, joined on emp_code column.

The two tabs have common report level filter applied - ‘Fortnight Period’ which is a slicer based on a column Period_end_date (it has the fortnight end dates) from Emp_Costing table.

Tab1 looks like following, its run by selecting particular employees for a fortnight:

tab1.PNG

I expect Tab2 looks like following:

tab2.PNG

 Basically, I want to auto populate 'Total fortnightly payment' column on tab2 from total of 'Amount' column on Tab1. Tab1 is run for each fortnight and the total amount populates in Total fortnightly payment column (tab2) next to the corresponding fortnight period. 

 I created two tabs because the employees selected to run the report, can vary for each fortnight. So, I run tab1 for each fortnight by selecting specific employees and populate the totals on tab2 that consolidates fortnightly totals. Say I run tab1 for fortnight ending on 05/05/2019 & refer the total on tab2 to populate it against period end date 05/05/2019. Then I run tab1 for fortnight ending on 19/05/2019  & refer the total on tab2 to populate it against period end date 19/05/2019  and so on...

I am wondering if there is a formula in DAX that I can use to refer to the fortnightly total on tab1 in tab2? Or if there is a better way to achieve my desired result?

 

Many Thanks in advance,

Meena

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Need help in referring to a value on different tab of the same report

hi, @Meena 

You may try this way as below:

Step1:

You need to define a dim week ending table first.

And do not create a relationship with any table.

Step2:

Use this formula to create a measure

Total fortnightly payment = 
VAR _lastdate =
    CALCULATE (
        MAX ( 'Dim week ending'[Week ending] ),
        FILTER (
            ALLSELECTED ( 'Dim week ending'[Week ending] ),
            'Dim week ending'[Week ending] < MAX ( 'Dim week ending'[Week ending] )
        )
    )
RETURN
    IF (
        MAX ( 'Dim week ending'[Week ending] ) <= MAX ( 'Table'[Date] ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Date] ),
                'Table'[Date] <= MAX ( 'Dim week ending'[Week ending] )
                    && 'Table'[Date] > _lastdate
            )
        )
    )

Then just do some adjustments by your data model.

and here is pbix file, please try it.

 

By the way, I would suggest you add a date table then create a relationship with ‘Fortnight Period’ which is a slicer based on a column Period_end_date. then use date table filed as a slicer. 

And change the MAX ( 'Table'[Date] ) to date table field in formula.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Need help in referring to a value on different tab of the same report

hi, @Meena 

You may try this way as below:

Step1:

You need to define a dim week ending table first.

And do not create a relationship with any table.

Step2:

Use this formula to create a measure

Total fortnightly payment = 
VAR _lastdate =
    CALCULATE (
        MAX ( 'Dim week ending'[Week ending] ),
        FILTER (
            ALLSELECTED ( 'Dim week ending'[Week ending] ),
            'Dim week ending'[Week ending] < MAX ( 'Dim week ending'[Week ending] )
        )
    )
RETURN
    IF (
        MAX ( 'Dim week ending'[Week ending] ) <= MAX ( 'Table'[Date] ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table'[Date] ),
                'Table'[Date] <= MAX ( 'Dim week ending'[Week ending] )
                    && 'Table'[Date] > _lastdate
            )
        )
    )

Then just do some adjustments by your data model.

and here is pbix file, please try it.

 

By the way, I would suggest you add a date table then create a relationship with ‘Fortnight Period’ which is a slicer based on a column Period_end_date. then use date table filed as a slicer. 

And change the MAX ( 'Table'[Date] ) to date table field in formula.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Meena Member
Member

Re: Need help in referring to a value on different tab of the same report

Thanks a lot @v-lili6-msft  Your solution perfectly works for me!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 414 members 3,895 guests
Please welcome our newest community members: