Forum Discussion

smstrickland's avatar
smstrickland
Helper 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_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.

 

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.

5 Replies

    • smstrickland's avatar
      smstrickland
      Helper 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
        Community 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.