topic Re: Calculate days in DAX Commands and Tips

Calculate days

Goudi — Sat, 01 Jul 2023 16:57:31 GMT

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

Re: Calculate days

Anonymous — Mon, 03 Jul 2023 02:17:11 GMT

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.

Re: Calculate days

Goudi — Mon, 03 Jul 2023 11:04:07 GMT

Thanks Stephen!!

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

Re: Calculate days

Anonymous — Tue, 04 Jul 2023 01:37:26 GMT

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.

Re: Calculate days

Goudi — Tue, 04 Jul 2023 07:17:51 GMT

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

Re: Calculate days

Anonymous — Tue, 11 Jul 2023 05:48:20 GMT

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.

Re: Calculate days

Goudi — Tue, 11 Jul 2023 09:56:09 GMT

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,

Re: Calculate days

Anonymous — Wed, 12 Jul 2023 03:07:53 GMT

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.

Re: Calculate days

Goudi — Wed, 12 Jul 2023 10:18:12 GMT

Hi Anonymous ,

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-_q9iedWWq68whpS4w?e=gfbrDC

Thanks for your support!

Re: Calculate days

Anonymous — Tue, 18 Jul 2023 02:45:14 GMT

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.

Re: Calculate days

Goudi — Tue, 18 Jul 2023 08:23:43 GMT

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.

Re: Calculate days

Anonymous — Wed, 19 Jul 2023 08:35:47 GMT

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.

Re: Calculate days

Goudi — Thu, 20 Jul 2023 08:42:55 GMT

Hi Anonymous ,

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