Forum Discussion

Macc-PA's avatar
Macc-PA
Helper I
5 years ago
Solved

filtering rows using dates from another table

I am new to powerbi and DAX. I am struggling with how todo the following:

 

I have a packages table which has a shipped date and a received date for each package.


I have a calendar which is used as slicer to select a date range which is of interest.

 

I need to calculate how many packages are still in transit each month. something like the following:
For each month in the calendar table
{
if packages.shipDate < calendar.mth and packages.recvDate > calendar.mth
then count++
}

Need Help
  • Macc-PA's avatar
    Macc-PA
    5 years ago

    My relationship between transit and dates tables is:

     

    Because there is a relationship between the tables, I had to do the following so that it didnt use the relationship to calculate the measure you'd suggested.

    PackageInTransit2 = 
    CALCULATE(
     IF( MAX(Transit2[Date Shipped] ) <= [SelectedMonth2]
     && MAX(Transit2[Date Recv] ) > [SelectedMonth2]
     /* Then */ , 1
     /* Else */ , IF( MAX(Transit2[Date Recv]) = [SelectedMonth2]
                  /* Then */ , 2
                  /* Else */ , 0 
                  ) 
     ),
     CROSSFILTER ( ZMyCalendar[Date], Transit2[Date Recv], NONE )
    )
     
    SelectedMonth2 =  SELECTEDVALUE( ZMyCalendar[MonthInCalendar] )

     

    The result is as below showing what has been delivered in april and what is outstanding

     

    Thank you Dedmon

     

8 Replies