Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |