calendar with two dates with day hours and min

[danielgomv]

Hi im very confused and frustated, but wnat to continue learning anb understand new things ! . 

currently im stsart of learning i have a technical test for a job , and im very confuse and i cant realize a point becouse y cant create a correct calendar table, i have a two dates one of out of bikes and another to in bikes, this is a rental. And have an hours and minuts.    and for example, some bikes out one day and back another, and have a lot of diferent hours in some day. and i cant made a correct calendar if i made a calendar with start date  the measures with this are corrects but the end date are incorrects. the solution is have two calendar tables or what ?

if someone can help me im very greateful !

[DataNinja777]

Hi  @danielgomv,

 

The key to solving your problem is to set your calendar table as a disconnected table from your fact table, with separate start and end date fields. Setting the calendar table as a disconnected table is the most effective way to accurately and flexibly analyze two date fields representing the bike lending period's duration.

Additionally, since your analysis requires both date and time, I recommend separating the time field from the date field to avoid unnecessarily high cardinality.

Best regards,

[danielgomv]

@DataNinja777 

i was have the  conection active beetween value and start date and inactive in end date 

and this is the correct form 

how i made the calendar? i was use this code based in the start and end date ,, but i dont know if this is the correct form for take all date with houers and mins, and make measures for example, calculate the net rate per day and hour have a lot of problems .  i check the  procces in python and i have some bad in power bi 

Calendar =

// Extiende el calendario para abarcar el rango completo de fechas de Start Date y End Date

VAR StartDate =

    MINX ( trip_data, MIN ( trip_data[Start Date], trip_data[End Date] ) )

VAR EndDate =

    MAXX ( trip_data, MAX ( trip_data[Start Date], trip_data[End Date] ) )

RETURN

    ADDCOLUMNS (

        GENERATESERIES ( StartDate, EndDate, TIME ( 1, 0, 0 ) ),

        // Incrementa cada hora

        "Year", YEAR ( [Value] ),

        "Month", FORMAT ( [Value], "MMMM" ),

        "MonthNumber", MONTH ( [Value] ),

        "Day", DAY ( [Value] ),

        "DayOfWeek", FORMAT ( [Value], "dddd" ),

        "Hour", HOUR ( [Value] ),

        "Quarter", "Q" & QUARTER ( [Value] ),

        "FormattedDateTime", FORMAT ( [Value], "MM/dd/yyyy " ) // Formato de fecha y hora

    )  

thanks for your time and reply 

[danielgomv]

Thanks for reply 

made it of this form 

but how i can made the canlendar with all hours and minutes, this is teh code that i use 

Calendar =

// Extiende el calendario para abarcar el rango completo de fechas de Start Date y End Date

VAR StartDate =

    MINX ( trip_data, MIN ( trip_data[Start Date], trip_data[End Date] ) )

VAR EndDate =

    MAXX ( trip_data, MAX ( trip_data[Start Date], trip_data[End Date] ) )

RETURN

    ADDCOLUMNS (

        GENERATESERIES ( StartDate, EndDate, TIME ( 1, 0, 0 ) ),

        // Incrementa cada hora

        "Year", YEAR ( [Value] ),

        "Month", FORMAT ( [Value], "MMMM" ),

        "MonthNumber", MONTH ( [Value] ),

        "Day", DAY ( [Value] ),

        "DayOfWeek", FORMAT ( [Value], "dddd" ),

        "Hour", HOUR ( [Value] ),

        "Quarter", "Q" & QUARTER ( [Value] ),

        "FormattedDateTime", FORMAT ( [Value], "MM/dd/yyyy " ) // Formato de fecha y hora

    )

im looking for your reply 

thanks a lot 

[DataNinja777]

Hi @danielgomv ,

You can achieve the desired output of flexibly displaying the duration of the bike lending period by using disconnected Calendar and Time tables.

The Calendar table is written in DAX as follows:

Calendar = calendar(min('Table'[Start Date]),max('Table'[End Date]))

A separate time table is written in dax as follows:

TimeTable = 
ADDCOLUMNS(
    GENERATESERIES(0, 1439, 1),
    "Time", TIME(INT([Value] / 60), MOD([Value], 60), 0),
    "Hour", INT([Value] / 60),
    "Minute", MOD([Value], 60),
    "Hour-Minute", FORMAT(TIME(INT([Value] / 60), MOD([Value], 60), 0), "hh:mm")
)

To separate the date and time fields, use the "Split Column" function in Power Query, selecting a space as the delimiter.

Finally, you can write the DAX measure for the trip duration as follows:

Trip Duration = 
VAR SelectedDate = MAX('Calendar'[Date])
VAR SelectedTime = MAX('TimeTable'[Time]) // Assume Time is in a format like "HH:MM"
VAR SelectedDateTime = SelectedDate + SelectedTime
RETURN
    SUMX(
        'Table',
        IF(
            'Table'[Start Date] + 'Table'[Start Time] <= SelectedDateTime
                && 'Table'[End Date] + 'Table'[End Time] >= SelectedDateTime,
            // Calculate the duration using max and min datetime in minutes
     1,blank()
        )
    )

I've added additional rows in the data table by adding random rows, and the resultant output will look like below:

I have attached an example pbix file.

Best regards,

[danielgomv]

@DataNinja777  THANKS FOR THE EXAMPLE I APRRECIETEED. 

I TRY TO COUTN THE NET RATE BEETWEEN EACH STATION 

Net Rate  =

VAR SelectedDate = MAX('Calendar'[Date])

VAR SelectedTime = MAX('TimeTable'[Time]) // Asume que Time está en formato "HH:MM"

VAR SelectedDateTime = SelectedDate + SelectedTime

VAR OutgoingTrips =

    SUMX(

        'Table',

        IF(

            'Table'[Start Date] + 'Table'[Start Time] <= SelectedDateTime &&

            'Table'[End Date] + 'Table'[End Time] >= SelectedDateTime &&

            'Table'[Start Station] = SELECTEDVALUE('Table'[Start Station]),

            1,

            BLANK()

        )

    )

VAR IncomingTrips =

    SUMX(

        'Table',

        IF(

            'Table'[Start Date] + 'Table'[Start Time] <= SelectedDateTime &&

            'Table'[End Date] + 'Table'[End Time] >= SelectedDateTime &&

            'Table'[End Station] = SELECTEDVALUE('Table'[End Station]),

            1,

            BLANK()

        )

    )

RETURN

    OutgoingTrips - IncomingTrips   

WITH THIS CODE BUT I CANT FIND THE FORM THAT THIS WORKS . I NEED THIS FOR CAN CALCULATE  THE ENT RATE BY HOUR DAY AND MONTHS 

[DataNinja777]

Hi @danielgomv,

In my opinion, the DAX formula above doesn’t provide meaningful information. It subtracts the duration filtered by 'End Station' from the duration filtered by 'Start Station,' which doesn’t seem logical. I assume that 'Start Station' and 'End Station' represent station IDs rather than the monetary rate charged. Please let me know if this assumption is incorrect.

Best regards,

[danielgomv]

Hi @DataNinja777 

yes this assumption is correct. but isnt for the monetary rate. but is similar, is for calculate the demand in speceific hour. for each station . 

(net rate is defined as trips ended minus trips started at the station for a given hour). That is, at any time the logistics team of the company should be able to make a statement such as 'In the next hour, the net stock of bikes at station A will change  by X'.

this is the request that i have . 

thanks for ypur time and for your  reply