Calculated Column: Return Values In Last 14 Days only

[HenryJS]

Hi all,

I have created a calculated column called 'CV:Job' (picture 3) in the Candidates table (picture 2) as per below.

I would like to filter this calculated column to only return values in the last 14 days (i.e. action dates in the last 14 days in Candidate Actions Table).

How can I ammend the DAX in picture 3 to solve this?

1. Candidate Actions Table

2. Candidates Table

3. CV:Job Calculated Column

Thanks,

Henry

[v-eachen-msft]

Hi @HenryJS ,

You can get data of the last 14 day with DATEDIFF() function and put it in the filter.

DATEDIFF('Candidate Actions'[Date Actions],CALCULATE(LASTDATE('Candidate Actions'[Date Actions]),ALLEXCEPT('Candidate Actions','Candidate Actions'[CandidateRef])),DAY)<=14)

[HenryJS]

@v-eachen-msft 

is this what you mean? If so it did not work

CV:Job = calculate(sum(CandidateActions[Activities]),FILTER(CandidateActions,CandidateActions[ActionName]="CV Sent" && CandidateActions[CandidateRef]=Candidates[CandidateRef] && DATEDIFF('Candidate Actions'[Date Action],CALCULATE(LASTDATE('Candidate Actions'[Date Action]),ALLEXCEPT('Candidate Actions','Candidate Actions'[CandidateRef])),DAY)<=14)))

[v-eachen-msft]

Hi @HenryJS ,

I found an extra parenthesis in your DAX. You need to remove it and use the unified table name.

CV:Job =
CALCULATE (
    SUM ( CandidateActions[Activities] ),
    FILTER (
        CandidateActions,
        CandidateActions[ActionName] = "CV Sent"
            && CandidateActions[CandidateRef] = Candidates[CandidateRef]
            && DATEDIFF (
                'CandidateActions'[Date Action],
                CALCULATE (
                    LASTDATE ( 'CandidateActions'[Date Action] ),
                    ALLEXCEPT ( 'CandidateActions', 'CandidateActions'[CandidateRef] )
                ),
                DAY
            ) <= 14
    )
)

If it doesn't work, you need to describe whether it shows incorrect result or DAX can't run properly.

[HenryJS]

@v-eachen-msft 

Thanks for your help. There are duplicate dates in the date column so could not run properly as per below.

[v-eachen-msft]

Hi @HenryJS ,

Try to replace LASTDATE() with MAX() function.

If it still doesn't work, you could share your sample data if you don't have any confidential information. Please upload your files to One Drive and share the link here.

[HenryJS]

Here is the relationship diagram