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

[Anonymous]

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:

I expect Tab2 looks like following:

 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

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

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

[Anonymous]

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