Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 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 ...
Need Help
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    5 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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 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
    5 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
      5 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..
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Does no one has a idea how to add a variable which i can use for filtereing?

         

        I just want to check if the start and end date of each vehicle is in between the current day..