Forum Discussion

jfenico's avatar
jfenico
Icon for Helper III rankHelper III
6 years ago
Solved

Finding next records based off closest due dates

Hi all, The data I am querying currently brings in a list of action items for a project. That project may have 10-20 action items. What we want to see are only the next two action items coming up ba...
Need Help
  • lc_finance's avatar
    lc_finance
    6 years ago

    Hi jfenico ,

     

     

    you can download my proposed solution from here.

     

    I added a calculated column with the following formula:

    Deadline = 
var currentProject = [p_key]
var currentDueDate = [Due Date]
var test = VALUES('ActionItems'[Due Date])
var futureDeadlines = FILTER('ActionItems','ActionItems'[Due Date]>=TODAY() && 'ActionItems'[p_key]=currentProject)
var deadline1 = MINX(futureDeadlines,[Due Date])
var deadline2 = MINX(FILTER('ActionItems','ActionItems'[Due Date]>deadline1 && 'ActionItems'[p_key]=currentProject),[Due Date])
var pastDeadline = MAXX(FILTER('ActionItems','ActionItems'[Due Date]<TODAY() && 'ActionItems'[p_key]=currentProject),[Due Date])

RETURN 
 SWITCH(currentDueDate 
 , deadline1, "Deadline 1"
 , deadline2, "Deadline 2"
 , pastDeadline, "Past Deadline")

     

    This column will show Deadline 1 for the next deadline, Deadline 2 for the deadline after, and Past deadline for the previous deadline.

    You can filter this column to only show the deadlines you are interested in.

     

     

    Does this help you?

     

    LC

    Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

jfenico's avatar
jfenico
Icon for Helper III rankHelper III
6 years ago

lc_finance 

Thanks LC, this is absolutely what I needed and you've saved me many sleepless nights.

I need to send you some X-Mas cookies or something 🙂

lc_finance's avatar
lc_finance
Icon for Solution Sage rankSolution Sage
6 years ago

Hi jfenico ,

 

 

I am glad about preventing you from some sleepless nights.

 

Thank you for the Christmas cookies proposal.

If you want, you could subscribe to my blog www.finance-bi.com, I want to share tips and tricks on DAX and Power BI to anyone interested. Of course, up to you.

 

Do not hesitate if you have any more questions.

 

LC