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...
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

     
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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.