Forum Discussion

smstrickland's avatar
smstrickland
Icon for Helper I rankHelper I
6 years ago
Solved

DAX formula Help

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_D...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

smstrickland's avatar
smstrickland
Icon for Helper I rankHelper I
6 years ago

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_IDHRS_DTENTERED_HRSPROJ_ID
CARR163/12/2020 0:003.5I9020.0000
CARR163/12/2020 0:000.5X2000.0000
CARR163/12/2020 0:000.5X9000.0001
CARR163/11/2020 0:001I9020.0000
CARR163/11/2020 0:003X2000.0000
CARR163/11/2020 0:003.5X9000.0001
CARR163/10/2020 0:000.25I9020.0000
CARR163/10/2020 0:001.5X2000.0000
CARR163/10/2020 0:000.75X9000.0000
CARR163/10/2020 0:005X9000.0001
CARR163/9/2020 0:000I9020.0000
CARR163/9/2020 0:003.75X2000.0000
CARR163/9/2020 0:003.75X9000.0001
CARR163/6/2020 0:000.25B2000.0000
CARR163/6/2020 0:007.25X2000.0000
CARR163/5/2020 0:000.5B2000.0000
CARR163/5/2020 0:000.25I2000.0000
CARR163/5/2020 0:000I9020.0000
CARR163/5/2020 0:005X2000.0000
CARR163/5/2020 0:001.75X9000.0001
CARR163/4/2020 0:007.5X2000.0000

 

Table: Timesheet_Line_History (contains posted hours only, aggregated per pay period)
TS_DTEMPL_IDENTERED_HRSPROJ_ID
2/28/2020 0:00CARR1650.75X2000.0000
2/28/2020 0:00CARR160.75I2528.0000
2/28/2020 0:00CARR1631I2000.0000
2/14/2020 0:00CARR165B2000.0000
2/14/2020 0:00CARR1623I9020.0000
2/14/2020 0:00CARR1615X9000.0001
2/14/2020 0:00CARR1632X2000.0000
1/31/2020 0:00CARR1660X2000.0000
1/31/2020 0:00CARR166I9020.0000
1/31/2020 0:00CARR169B2000.0000

 

And here is what the output would look like:

Expected Output   
EMPL_IDPROJ_IDPosted HoursNew Hours
Carr16X2000.0000142.7528.5
 I2525.00000.750
 I2000.0000310.25
 B2000.0000140.5
 I9020.0000294.75
 X9000.00011514.5
 X9000.000000.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's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

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.