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

Hi lc_finance

 

Thanks for getting this started.

You can download the PBIX file here - https://www.dropbox.com/s/lc32xvzmk7fivim/Action%20Item%20Tracker%20WSP.pbix?dl=0

 

A little more info - specifically what I am looking to do is grab the next action items (actionitem table) in line by the due date, per project, which is noted by the p_key. Each p_key is a different project. We want to be able to filter out one project at a time and see which two action items are coming due. 

 

Bonus points, but not as important is to be able to see the last due date that just passed. Let me know if you need clarification on the PBIX file.

lc_finance's avatar
lc_finance
Icon for Solution Sage rankSolution Sage
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

  • MattGell's avatar
    MattGell
    Icon for Advocate I rankAdvocate I
    6 years ago

    Hi LC
    this looks like something I need, I have been searching for a solution but to no avail.

    I have 2 tables table1 called "AVI_USER" with people with unique "UserID" as primary key. Table 2 is for reservations for those people called "AVI_RES_PAX". the start dates of the reservations are stored in "AVI_RES_PAX[REQ_FROM_DT]".

    Reservations can be for different modes of transport distinguished in "AVI_RES_PAX[REQ_MODE]".

    I simply want columns for the next reservation date for each mode per person and the a column to show the mode.

    Desired result below:

    UserID    NextFixedWingDate Origin Destination NextRotaryWingDate Origin Destination

    NM001     01-Sep-20              LHR     GIG               31-Aug-20                FTS      ABZ

    NM002     10-Oct-20              CDG    HOU              01-Nov-20               PPA     ABZ

    NM002     20-Aug-20             LOS     JOB                                                

     

    I know it look ssimple but I just cant get it

    Please help

    Matt