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?