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 many in September

 

Thanks!!

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

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Goudi ,

     

    You can create two measures to calculate the days in August and in September.

     

    Aug Days =
VAR _start =
    DATE ( 2023, 8, 1 )
VAR _end =
    DATE ( 2023, 8, 31 )
VAR _dep =
    MAX ( 'Table'[departure date] )
VAR _arr =
    MAX ( 'Table'[arrival date] )
VAR _days =
    IF (
        _dep >= _start,
        IF ( _arr >= _end, DATEDIFF ( _dep, _end, DAY ), DATEDIFF ( _dep, _arr, DAY ) ),
        IF (
            _arr >= _end,
            DATEDIFF ( _start, _end, DAY ),
            DATEDIFF ( _start, _arr, DAY )
        )
    )
RETURN
    IF ( _days > 0, _days )

    Sep Days =
VAR _start =
    DATE ( 2023, 9, 1 )
VAR _end =
    DATE ( 2023, 9, 30 )
VAR _dep =
    MAX ( 'Table'[departure date] )
VAR _arr =
    MAX ( 'Table'[arrival date] )
VAR _days =
    IF (
        _dep >= _start,
        IF ( _arr >= _end, DATEDIFF ( _dep, _end, DAY ), DATEDIFF ( _dep, _arr, DAY ) ),
        IF (
            _arr >= _end,
            DATEDIFF ( _start, _end, DAY ),
            DATEDIFF ( _start, _arr, 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

    Thanks Stephen!!

    I have copied your measure but it returns a blank value.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Goudi ,

       

      I see you want to put the measure into a card visual for showing the total. You need to create another measure to sum it up.

      Aug Total = SUMX('Table',[Aug 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

        Thanks again v-stephen-msft!!

        But it still gives a blank value (Fig.1), I have copied your 'Aug Days" measurement into a simpler table but it still gives a blank value (Fig.2).

         

        Fig. 1

         

         

         

        Fig.2

         

        Thanks fou your support!!