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_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
    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.