filtering rows using dates from another table

[Macc-PA]

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++
}

[v-deddai1-msft]

Hi @Macc-PA ,

Maybe you can try the measure below:

Measure =
CALCULATE (
    COUNT ( 'packages'[ID] ),
    FILTER (
        ALL ( 'packages' ),
        MONTH ( 'packages'[shipDate] ) < MONTH ( MIN ( CALENDAR[Date] ) )
            && MONTH ( 'packages'[recvDate] ) > MONTH ( MAX ( CALENDAR[Date] ) )
    )
)

If it doesn't work, please show us some sample data and your expexted output.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Macc-PA]

Thanks that produces the correct count, however I am having trouble seeing which packages are still in shipment as the filtering on rceived date only shows me the packages that month. I need to see which packages were in the count.

[v-deddai1-msft]

Hi @Macc-PA ,

You can create a measure in your table visual filter:

 Measure = IF( MONTH ( MAX('packages'[shipDate]) ) < MONTH ( MIN ( CALENDAR[Date] ) )
            && MONTH ( MAX('packages'[recvDate]) ) > MONTH ( MAX ( CALENDAR[Date] ) ),1,0)
    

Add it to the table visual and set it is 1:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Macc-PA]

The meaure you'd suggested always evaluates to zero regardless of which date I select from the left hand table to filter on and therefore shows all rows in the dataset.

[v-deddai1-msft]

Hi @Macc-PA ,

What is the relationship between calendar table and fact table? Would you please show us sample data (pbix file by onedrive for business)  that we can help you well.

Best Regards,

Dedmon Dai

[Macc-PA · Accepted solution]

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

[Greg_Deckler]

@Macc-PA - Sample data would help tremendously but Open Tickets may be what you are looking for. 
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@Macc-PA ,

countx(filter(packages, packages[shipDate] <= max(Date[Date]) && packages[recvDate ] > max(Date[Date])),packages[ID])

Both shipDate and recvDate join to date table . for above formula , the should be joined so use crossfilter

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...