Calculate days

[Goudi]

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

[Goudi]

Thanks Stephen!!

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

[v-stephen-msft]

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]

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

[v-stephen-msft]

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.           

[Goudi]

Hi @v-stephen-msft ,

I am a beginner in power bi but I believe that what I create is a measurement not a calculated column.

I put an example in which I include your measure and as you will see it does not return any value.

https://icbarcelona-my.sharepoint.com/:u:/p/jose_sanchez/EbedglRaKpxElRsJpNLweV0BNR_z-_q9iedWWq68whp... 

Thanks for your support!

[v-stephen-msft]

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]

Hi @v-stephen-msft ,

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.

[v-stephen-msft · Accepted solution]

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]

Hi @v-stephen-msft ,

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

[v-stephen-msft]

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]

Hi @v-stephen-msft ,

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,

[v-stephen-msft]

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.