Filter: Return Values In Last 14 Days Only

[HenryJS]

Hi all,

How can I add to the below FILTER formula to only return values in the last 14 days?

There is a Action Date in the Candidate Actions table (shown below).

I would like CV : Job to only return values in the last 14 days.

Thanks,

Henry

[v-diye-msft]

Hi @HenryJS 

If the above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

If the issue still exists, please kindly share more details, we'd like to provide further helps.

[amitchandak · Accepted solution]

Create a new column in the table

Date Action = format(Table[Action Date],"mm/dd/YYYY")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Then use a formula like

Lat 14 days = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[date],max(Sales[Sales Date]),-14,DAY)) 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[HenryJS]

Hi @amitchandak ,

Thanks for getting back to me.

I am wanting to only return 'CV:Job' (which is a calculated column in table 'Candidates') values for the last 14 days. The 'Action Date' column is in a different table called 'Candidate Actions'.

Am I able to edit the above code for 'CV:Job;' to return only values in last 14 days?

I cannot work out using previous responses.

Thanks,

Henry

[amitchandak]

Please share the relationship Diagram and the current formula in text format.

[HenryJS]

@amitchandak 

Please see below

CV:Job = calculate(sum(CandidateActions[Activities]),FILTER(CandidateActions,CandidateActions[ActionName]="CV Sent" && CandidateActions[CandidateRef]=Candidates[CandidateRef]))

[amitchandak]

I think you should add date calendar (https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions)

Try

 CV:Job = calculate(sum(CandidateActions[Activities]),FILTER(CandidateActions,CandidateActions[ActionName]="CV Sent" && CandidateActions[CandidateRef]=Candidates[CandidateRef])
 ,DATESINPERIOD('Date'[date],max(CandidateActions[Date Action]),-14,DAY) )

In case you do not have the calendar then use action date[action date] in place of date[date]

[Anonymous]

Perhaps create a "Days Difference"column that calculates the difference in days between today and your date field. Then you can create an "Is within 14 Days Flag" column, that sets the value to either 1 or 0 if the "Days Difference" column is -14 or greater(i.e. -14, -13, -12,...) and less than 0. Next, in your summation Measure, you can calculate the sum based on the "Is within 14 Days Flag" equal to 1.

Not sure if the timestamp within your Date field will give you problems with my suggestion but you can always create a new Date field that's formatted without the timestamp and then use this new Date field to perform the above.

[parry2k]

@HenryJS last 14 days from today?

[parry2k]

@HenryJS add date table in your model, there are many posts on how to add one. For time intelligence, date table make things easier to work.

- Mark date table as date. 

- Set relationship on this date table with your action table and then add following measure

Last 14 Days = 
CALCULATE ( 
SUM ( ActionTable[Activities] ),
DATESINPERIOD ( DateTable[Date], TODAY(), -14, DAY )
)

[HenryJS]

@parry2k I can't mark as date table because date column can only have one timestamp per day and the date column can't have gaps in dates

[parry2k]

@HenryJS this you are going to do on date table not your activity table