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 ,

 

I suddenly discovered today that you created a calculated column. My method creates measures, not calculated columns. There is a difference between the two. Try creating a measure.

Power BI Column vs Measure. Which one should I use and when? | by Peter Hui | Towards Data Science

 

                                                                                                                                                         

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.