Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Check if date from one table lies between two dates from another table

Hi everyone,

 

i'm quite new to power BI and have some troubles understanding how DAX works.. 

 

I think my problem is quite simple:

Table Date just includes dates, calender weeks and so on and is just used to filter other tables within the model. I have a filter were i can select a period. 

 

Table Vehicle is a list of vehicle numbers ech vehicle has a start and an end date. This table is used to filter other tables with vehicle numbers in the model. 

 

Now i want to check for every day of the filtered period if date lies in between start and end date for each vehicle. I tried to use an if function, but cant get it to work. My goal is a new measure, if date lies in between start and end date of vehicle table then "yes" else "no". 

My goal is to use this measure as a input filter, so i only have the vehicle numbers where date lies in between start and end date when i choose "yes"..

 

best regards

Need Help
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    Sorry for replying late. If you just want to check whether the current day(I suppose it as totay() variable) is between the begin date and end date, you can create this measure:

    Check today = 
IF (
    TODAY () >= SELECTEDVALUE ( 'Table'[GW-Begin] )
        && TODAY () <= SELECTEDVALUE ( 'Table'[GW-end] ),
    "yes",
    "no"
)

    If it is a date range with a single date table, create this measure:

    Check GW = 
VAR _max =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR _min =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
RETURN
    IF (
        _min >= SELECTEDVALUE ( 'Table'[GW-Begin] )
            && _max <= SELECTEDVALUE ( 'Table'[GW-end] ),
        "yes",
        "no"
    )

     

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , you have try something like

     

    calculate(Countrows(Table), filter(Table,Table[Date]>=min(Vehicle[Start Date]) && Table[Date]<=max(Vehicle[Start Date])))

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      thanks for your answer, i don't want this function "hard coded" like this. I just want a new variable which checks if a vehicle is under warranty for each selected day. So that i have the possibility to use this new measure as a filter. 

       

      This is the vehicle list with a vehicle number and a start of warranty and end of warranty date:

       

      As i said before i use a Date Table which just includes dates until 2030 to use this as a filter. I read somwhere that this is a prober way. 

      So i created a cross table with date (Datum) and the vehicle numbers:

       

      Now i want to have a yes or no as a value if the Date is in between the start and end date of the vehicle list.

       

      best regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        It seems that this is not as simple as i thougt.. 

         

        I created a list with m code where every vehicle has a column with all dates between start and end date. Then i tried to connect my Date table with the Dates in the vehicle table. But thats not possible because i alredy use the Date table to filter other tables.. 

         

        So i tried another thing which i need. I need to calculate the runtime per month, which is the sum of vehicles where the date lies between the start and end date multiplied by a factor. The sum of all this values within a month is the runtime per month.

        first i tried to count how many vehicles are "active" at one day:
        VehicleInGW =
        CALCULATE(COUNT(N_Vehicle[Betriebsnr.]);
        FILTER(N_Vehicle;N_Vehicle[GW-Beginn]<= MIN(Datumstabelle[Datum])))
         
        This works, but only if i create a table with Date.. So now i want to sum all those values per day and display them in a table with month. My Date table has a column for month. But i cannot just create another measure which sums the VehicleInGW measure..