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

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

Hi jfenico ,

 

 

this is possible by doing the following:

1) first, filtering the action items to only have future action items. You can do that by filtering for dates > TODAY()

2) second, find the "smallest" of the future dates (you can use FIRSTDATE()), for each project

 

Does this help you?

If you can share a sample Power BI file, I'd be happy to help you more on implementing the correct calculation.

 

Regards

 

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