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 ,

 

 

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