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

[Anonymous]

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

[amitchandak]

@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]

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]

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]

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

[v-yingjl · Accepted solution]

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.