Forum Discussion

Goudi's avatar
Goudi
Frequent Visitor
3 years ago
Solved

Calculate days

Hi!! I have this report: I have created the measure 'stay days' by subtracting the departure date minus the arrival date but I would like to differentiate how many days are in August and how ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Goudi ,

     

    Thanks for your patience. Please check this:

    Aug Days = 
VAR _start =
    DATE ( 2023, 8, 1 )
VAR _end =
    DATE ( 2023, 8, 31 )
VAR _dep =
    MAX ( 'Hoja1'[DEPARTURE] )
VAR _arr =
    MAX ( 'Hoja1'[ARRIVAL] )
VAR _days =
    IF (
        _arr >= _start,
        IF ( _dep >= _end, DATEDIFF ( _arr, _end, DAY ), DATEDIFF ( _arr, _dep, DAY ) ),
        IF (
            _dep >= _end,
            DATEDIFF ( _start, _end, DAY ),
            DATEDIFF ( _start, _dep, DAY )
        )
    )
RETURN
 IF ( _days > 0, _days )

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

    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
3 years ago

Hi Goudi ,

 

 

After research, it was my measurement _arr and _dep reversed, and here are the correct results.

Hope that helps.

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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

Goudi's avatar
Goudi
Frequent Visitor
3 years ago

Hi Anonymous ,

 

Thanks again, but it doesn't work, as you can see in your example, the booking with ID 2 gives a total of 36 days in August.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Goudi ,

     

    Thanks for your patience. Please check this:

    Aug Days = 
VAR _start =
    DATE ( 2023, 8, 1 )
VAR _end =
    DATE ( 2023, 8, 31 )
VAR _dep =
    MAX ( 'Hoja1'[DEPARTURE] )
VAR _arr =
    MAX ( 'Hoja1'[ARRIVAL] )
VAR _days =
    IF (
        _arr >= _start,
        IF ( _dep >= _end, DATEDIFF ( _arr, _end, DAY ), DATEDIFF ( _arr, _dep, DAY ) ),
        IF (
            _dep >= _end,
            DATEDIFF ( _start, _end, DAY ),
            DATEDIFF ( _start, _dep, DAY )
        )
    )
RETURN
 IF ( _days > 0, _days )

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

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

  • Goudi's avatar
    Goudi
    Frequent Visitor
    3 years ago

    Hi Anonymous ,

    It works! Thank you very much! you have been very kind.