DAX formula Help

[smstrickland]

I have a report with 2 tables: 'Timesheet_Cell' and 'Timesheet_Line_History.' The 'Timesheet_Cell' table contains hours entered into timesheets per day (applicable fields are [Entered_HRS] and [HRS_DT]) while the 'Timesheet_Line_History' table contains hours entered by pay period up to the last pay period that was posted (applicable field is [TS_DT]). I would like to create a measure within my report that tells me the number of hours that have been entered into a timesheet ([Entered_HRS]) since the last time that a pay period was posted ([TS_DT]).

For example, the last pay period posted was 2/28/2020 and 50 hours have been posted to Project A (this is time found in the 'Timesheet_Line_History' table). Since 2/28/2020, 10 additional hours have been entered into a timesheet (this is time found in the 'Timesheet_Cell' table; this table also contains the 50 hours found in the 'Timesheet_Line_History' table). Is there a way to filter the 'Timesheet_Cell' table to only return hours that have been entered after the latest date in 'Timesheet_Line_History'[TS_DT]?

BTW, I know that I could manually filter 'Timesheet_Cell'[HRS_DT] to be after 2/28/2020, but doing that means that I have to update that filter every time a new timesheet is posted. I would prefer to have a way for the date to be filtered automatically.

Thank you for any help that can be provided.

[amitchandak]

@smstrickland ,

Can you share sample data and sample output.

[smstrickland]

@amitchandak Here is sample data from the two tables:

Table: Timesheet_Cell (contains daily hours so includes all data in the Timesheet_Line_History table)
EMPL_ID	HRS_DT	ENTERED_HRS	PROJ_ID
CARR16	3/12/2020 0:00	3.5	I9020.0000
CARR16	3/12/2020 0:00	0.5	X2000.0000
CARR16	3/12/2020 0:00	0.5	X9000.0001
CARR16	3/11/2020 0:00	1	I9020.0000
CARR16	3/11/2020 0:00	3	X2000.0000
CARR16	3/11/2020 0:00	3.5	X9000.0001
CARR16	3/10/2020 0:00	0.25	I9020.0000
CARR16	3/10/2020 0:00	1.5	X2000.0000
CARR16	3/10/2020 0:00	0.75	X9000.0000
CARR16	3/10/2020 0:00	5	X9000.0001
CARR16	3/9/2020 0:00	0	I9020.0000
CARR16	3/9/2020 0:00	3.75	X2000.0000
CARR16	3/9/2020 0:00	3.75	X9000.0001
CARR16	3/6/2020 0:00	0.25	B2000.0000
CARR16	3/6/2020 0:00	7.25	X2000.0000
CARR16	3/5/2020 0:00	0.5	B2000.0000
CARR16	3/5/2020 0:00	0.25	I2000.0000
CARR16	3/5/2020 0:00	0	I9020.0000
CARR16	3/5/2020 0:00	5	X2000.0000
CARR16	3/5/2020 0:00	1.75	X9000.0001
CARR16	3/4/2020 0:00	7.5	X2000.0000

Table: Timesheet_Line_History (contains posted hours only, aggregated per pay period)
TS_DT	EMPL_ID	ENTERED_HRS	PROJ_ID
2/28/2020 0:00	CARR16	50.75	X2000.0000
2/28/2020 0:00	CARR16	0.75	I2528.0000
2/28/2020 0:00	CARR16	31	I2000.0000
2/14/2020 0:00	CARR16	5	B2000.0000
2/14/2020 0:00	CARR16	23	I9020.0000
2/14/2020 0:00	CARR16	15	X9000.0001
2/14/2020 0:00	CARR16	32	X2000.0000
1/31/2020 0:00	CARR16	60	X2000.0000
1/31/2020 0:00	CARR16	6	I9020.0000
1/31/2020 0:00	CARR16	9	B2000.0000

And here is what the output would look like:

Expected Output
EMPL_ID	PROJ_ID	Posted Hours	New Hours
Carr16	X2000.0000	142.75	28.5
	I2525.0000	0.75	0
	I2000.0000	31	0.25
	B2000.0000	14	0.5
	I9020.0000	29	4.75
	X9000.0001	15	14.5
	X9000.0000	0	0.75

I originally created this measure that calculated the new hours:

         SUM('Timesheet_Cell'[Entered_HRS]) - SUM('Timesheet_Line_History'[Entered_HRS])

and that worked until I started filtering the data by the 'Timesheet_Line_History'[TS_DT] field (there will sometimes be a need to see the posted hours by the pay period in which they were posted). When New Hours were calculated with the above formula, they would change to reflect all of the hours outside the date range of the selected pay period, but they should always reflect the hours entered after the latest posted date (in the sample data above that is 2/28/2020).

I am hoping to find a way to allow for the Posted Hours to be filtered by pay period while keeping the New Hours static. As I said before, I know I can do that by manually filtering 'Timesheet_Cell'[HRS_DT] to be after the latest 'Timesheet_Line_History'[TS_DT], but I would really like to know if there is a way to automate the filtering.

I'm not sure if it will matter, but these are only 2 of the tables in this report. There are quite a few others that either provide data for another column that is in the report or are needed in order to make the relationships between the tables work. For the two tables I mention here, there is an inactive many-to-many relationship on the PROJ_ID field.

Again, I appreciate any help that can be provided.

--Shauna

[v-juanli-msft · Accepted solution]

Hi @smstrickland 

Relationship as below

Create measures

new hours = SUM(Timesheet_Cell[ENTERED_HRS])+0.0

post hours =
CALCULATE (
    SUM ( Timesheet_Line_History[ENTERED_HRS] ) + 0.0,
    FILTER (
        Timesheet_Line_History,
        Timesheet_Line_History[TS_DT]
            <= MAX ( 'date'[Date] )
            && Timesheet_Line_History[TS_DT]
                >= MIN ( 'date'[Date] )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[smstrickland]

@Greg_Deckler Thank you for this link; it was very helpful.