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

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..
 
Anonymous's avatar
Anonymous
Not applicable
6 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..

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    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.