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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Goudi ,

     

    This may depend on the model, your data model should be different from mine, or you have some other filter that affects the results. If you could, please provide more details.

     

                                                                                                                                                             

    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 ,

       

      First of all thanks for you support.

       

      The objective is to create a report that shows by booking date and by channel the bookings made in a given month for a given month (for example, I would like to know which bookings were made in June for August and calculate de number of room night). To do this I insert, in the report, a table with the following fields:

      - Channel
      -Confirmation number
      -date on which the reservation was made
      -arrival date
      -departure date
      - and I would like to include number of rooms per night and per month (this is the origin of my inquiry)

      I would also like to include a visual card that adds up the total number of nights per month.

      For this I apply the filters: channel, booking date and arrival date.

      I hope I have explained myself correctly.

       

       

      Thanks,



       

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