How to compare two dates from different tables and populate the output

[POSPOS]

Hi All,

I have a requirement as below.

I have a table called as  "Final Table". In this table I need to add a new derived column called as "Total Hours"

Sample file is attached here

Final Table:

Logic for "Total Hours" :  I need to check the "lookup table" (shown below).

For each ID from the final table, take each "Pay Period End Data"  and check the "Current Date" column in the "Lookup Table".

eg: for ID 2000,

 1) first data is 6/16/2024. Check this in the lookup table "current date" column and aggregate all the hours <=6/16/2024.. In this case, there is no date less than 6/16/2024 then leave it blank.

2) next date is 6/30/2024. Again, check this in the current date column i.e., check for all dates greater than the previous date and less than or equal to the pay period end date, that is >6/17/2024 and <=6/30/2024 and then aggregate the Number of hours, so it will be 6/21+6/24+6/25+6/26+6/28=>8.25+9.25+9.25+9.00+8.25..

3) next date us 7/14/2024. Here, the dates will be >6/30/2024 and <=7/14/2024

Repeat this for every ID.

Lookup Table:

Lookup table will gave full history data

Can someone please advise on how to acheive this?

Thank You.

[Praful_Potphode]

Hi @POSPOS 
Please try the solution suggested by @DataInsights  

I have taken cue from the same solution and created measure which also give similar results.

Hours = 
var max_date=MAX('data-1'[Pay Period End Date])
var prev_date=MAXX(FILTER(ALL('data-1'),'data-1'[Pay Period End Date]<max_date),'data-1'[Pay Period End Date])
RETURN 
CALCULATE(SUM('data-2'[Sum of Number of hours]),
'data-2'[Current Date]>prev_date
,'data-2'[Current Date]<=max_date
)

for this to work i created a relationship between two tables using ID column(this is a many to many relationship  with cross filter direction set to both)

Thanks and Regards,

Praful

[DataInsights · Accepted solution]

@POSPOS,

Try this calculated column in table 'data-1':

Total Hours = 
VAR vID = 'data-1'[ ID]
VAR vPayPeriodEndDate = 'data-1'[Pay Period End Date]
VAR vPayPeriodEndDatePrevious =
    MAXX (
        FILTER (
            'data-1',
            'data-1'[ ID] = vID
                && 'data-1'[Pay Period End Date] < vPayPeriodEndDate
        ),
        'data-1'[Pay Period End Date]
    )
VAR vResult =
    SUMX (
        FILTER (
            'data-2',
            'data-2'[ID] = vID
                && 'data-2'[Current Date] > vPayPeriodEndDatePrevious
                && 'data-2'[Current Date] <= vPayPeriodEndDate
        ),
        'data-2'[Sum of Number of hours]
    )
RETURN
    vResult

[POSPOS]

@DataInsights  - Thank you for providing this solution. It worked!!