Complicated Time Intelligent DAX

[msarfaraz]

I have a complicated case and need your help, its table which connected in BI, the first column is system date and 2nd column is the arrival of guest and third is the departure of the guest, normally system count as stay period i.e. if guest arrival on 29th of Dec 2019 and departure on 1st of Jan 2020 stay night should be 0 (since arrival is on last month and departure on 1st of the month so stay night did not count in current month), however, if arrival on 28th Jan 2020 and departure on 05th Feb system will count only 4 days (28,29,30 and 31st), is there a way we can do the DAX on that. an example is an above scenario.

 

[JumpingCrab]

I think you would be able to use the EOMONTH() function to do this. It allows you to figure out how many days you have until the end of the month, but it can be used to find days from beginning of the previous month as well.

 

[msarfaraz]

can u explain further, as I am not able to do it

 

[JumpingCrab]

Sorry, I had not noticed that you responded. Here is my belated answer (in case you have not solved it yet).
The EOMONTH() function allows you to find the start and end of the month your system date is in. Once you have the interval ends, you can calculate how many days of the stay fall in the interval.

Start of the active month: =EOMONTH([@[SYSTEM DATE]],-1)+1
Start of the next month: =EOMONTH(@[SYSTEM DATE],0)+1

You have to check if the stay is overlapping the interval. This is true if the following condition is true:
AND([@Departure]>=EOMONTH([@[SYSTEM DATE]],-1)+1,[@Arrival]<EOMONTH([@[SYSTEM DATE]],0)+1)

Then you can use the MAX and MIN functions with the start and end dates to calculate the time in the period.
Putting it all together you get the following formula in the "stays period" column of your table:

=IF(AND([@Departure]>=EOMONTH([@[SYSTEM DATE]],-1)+1,[@Arrival]<EOMONTH([@[SYSTEM DATE]],0)+1),MIN([@Departure],EOMONTH([@[SYSTEM DATE]],0)+1)-MAX([@Arrival],EOMONTH([@[SYSTEM DATE]],-1)+1),0)

I hope this helps.

Book1
	A	B	C	D	E
1	SYSTEM DATE	Arrival	Departure	Stays Period	Total Nights Stays
2	1-Jan-20	29-Dec-19	1-Jan-20	0	3
3	2-Jan-20	30-Dec-19	2-Jan-20	1	3
4	3-Jan-20	31-Dec-19	3-Jan-20	2	3
5	4-Jan-20	1-Jan-20	4-Jan-20	3	3
6	5-Jan-20	2-Jan-20	5-Jan-20	3	3
7	6-Jan-20	3-Jan-20	6-Jan-20	3	3
8	7-Jan-20	4-Jan-20	7-Jan-20	3	3
9	8-Jan-20	5-Jan-20	8-Jan-20	3	3
10	9-Jan-20	6-Jan-20	9-Jan-20	3	3
11	10-Jan-20	7-Jan-20	10-Jan-20	3	3
12	11-Jan-20	8-Jan-20	11-Jan-20	3	3
13	12-Jan-20	9-Jan-20	12-Jan-20	3	3
14	13-Jan-20	10-Jan-20	13-Jan-20	3	3
15	14-Jan-20	11-Jan-20	14-Jan-20	3	3
16	15-Jan-20	12-Jan-20	15-Jan-20	3	3
17	16-Jan-20	13-Jan-20	16-Jan-20	3	3
18	17-Jan-20	14-Jan-20	17-Jan-20	3	3
19	18-Jan-20	15-Jan-20	18-Jan-20	3	3
20	19-Jan-20	16-Jan-20	19-Jan-20	3	3
21	20-Jan-20	17-Jan-20	20-Jan-20	3	3
22	21-Jan-20	18-Jan-20	21-Jan-20	3	3
23	22-Jan-20	19-Jan-20	22-Jan-20	3	3
24	23-Jan-20	20-Jan-20	23-Jan-20	3	3
25	24-Jan-20	21-Jan-20	24-Jan-20	3	3
26	25-Jan-20	22-Jan-20	25-Jan-20	3	3
27	26-Jan-20	23-Jan-20	5-Feb-20	9	13
28	27-Jan-20	24-Jan-20	27-Jan-20	3	3
29	28-Jan-20	25-Jan-20	28-Jan-20	3	3
30	29-Jan-20	26-Jan-20	31-Jan-20	5	5
31	30-Jan-20	27-Jan-20	5-Feb-20	5	9
32	31-Jan-20	28-Jan-20	5-Feb-20	4	8
33	Total				116
Sheet1

Cell Formulas
Range	Formula
D2:D32	D2	=IF(AND([@Departure]>=EOMONTH([@[SYSTEM DATE]],-1)+1,[@Arrival]<EOMONTH([@[SYSTEM DATE]],0)+1),MIN([@Departure],EOMONTH([@[SYSTEM DATE]],0)+1)-MAX([@Arrival],EOMONTH([@[SYSTEM DATE]],-1)+1),0)
E2:E32	E2	=[@Departure]-[@Arrival]
E33	E33	=SUBTOTAL(109,[Total Nights Stays])