Stay night in same month

[msarfaraz]

What would be the dax in column "Stay nights in the same month as per system date"

System Date	Arrival	Departure	Stay nights in the Same month as per system date
o1 Jan 2020	29th Dec 2019	01 Jan 2020	The answer should be 0
12 Jan 2020	31st Dec 2019	05th Jan 2020	The answer should be 4
28 Jan 2020	25th Dec 2019	10th Jan 2020	The answer should be 9

 

[sandy666]

Could you post more examples with expected results with a logic?

 

[msarfaraz]

Could you post more examples with expected results with a logic?

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.

 

[msarfaraz]

Logic could be Departure - Arrival if the date of the system is before the arrival or equal, otherwise system date - Departure

 

[sandy666]

could you post copyable example instead of picture which is useless.
try with XL2BB

 

[sandy666]

it could be like this

System Date	Arrival	Departure	Nights
01/01/2020	29/12/2019	01/01/2020	3
02/01/2020	30/12/2019	02/01/2020	3
03/01/2020	31/12/2019	03/01/2020	3
04/01/2020	01/01/2020	04/01/2020	3
05/01/2020	02/01/2020	05/01/2020	3
06/01/2020	03/01/2020	06/01/2020	3
07/01/2020	04/01/2020	07/01/2020	3
08/01/2020	05/01/2020	08/01/2020	3
09/01/2020	06/01/2020	09/01/2020	3
10/01/2021	07/01/2020	10/01/2020	System > Departure
11/01/2020	08/01/2020	11/01/2020	3
12/01/2020	09/01/2020	12/01/2020	3
13/01/2020	10/01/2020	13/01/2020	3
14/01/2020	11/01/2020	14/01/2020	3
15/01/2020	12/01/2020	15/01/2020	3
16/01/2020	13/01/2020	16/01/2020	3
17/01/2020	14/01/2020	17/01/2020	3
18/01/2020	15/01/2020	18/01/2020	3
19/01/2020	16/01/2020	19/01/2020	3
20/01/2020	17/01/2020	20/01/2020	3
21/01/2020	18/01/2020	21/01/2020	3
22/01/2020	19/01/2020	22/01/2020	3
23/01/2020	20/01/2020	23/01/2020	3
24/01/2020	21/01/2020	24/01/2020	3
25/01/2020	22/01/2020	25/01/2020	3
26/01/2020	23/01/2020	05/02/2020	13
27/01/2020	24/01/2020	27/01/2020	3
28/01/2020	25/01/2020	28/01/2020	3
29/01/2020	26/01/2020	31/01/2020	5
30/01/2020	27/01/2020	05/02/2020	9
31/01/2020	28/01/2020	05/02/2020	8

text value should be change to 0 or null if you want to use numeric values from Nights column

is this OK ?

I'll try with Stays Period now.......

btw. I'm not doing it with DAX so let me know if you really want DAX

 

[sandy666]

result

System Date	Arrival	Departure	Stays Period	Total Nights
01/01/2020	29/12/2019	01/01/2020	0	3
02/01/2020	30/12/2019	02/01/2020	1	3
03/01/2020	31/12/2019	03/01/2020	2	3
04/01/2020	01/01/2020	04/01/2020	3	3
05/01/2020	02/01/2020	05/01/2020	3	3
06/01/2020	03/01/2020	06/01/2020	3	3
07/01/2020	04/01/2020	07/01/2020	3	3
08/01/2020	05/01/2020	08/01/2020	3	3
09/01/2020	06/01/2020	09/01/2020	3	3
10/01/2021	07/01/2020	10/01/2020	3	System > Departure
11/01/2020	08/01/2020	11/01/2020	3	3
12/01/2020	09/01/2020	12/01/2020	3	3
13/01/2020	10/01/2020	13/01/2020	3	3
14/01/2020	11/01/2020	14/01/2020	3	3
15/01/2020	12/01/2020	15/01/2020	3	3
16/01/2020	13/01/2020	16/01/2020	3	3
17/01/2020	14/01/2020	17/01/2020	3	3
18/01/2020	15/01/2020	18/01/2020	3	3
19/01/2020	16/01/2020	19/01/2020	3	3
20/01/2020	17/01/2020	20/01/2020	3	3
21/01/2020	18/01/2020	21/01/2020	3	3
22/01/2020	19/01/2020	22/01/2020	3	3
23/01/2020	20/01/2020	23/01/2020	3	3
24/01/2020	21/01/2020	24/01/2020	3	3
25/01/2020	22/01/2020	25/01/2020	3	3
26/01/2020	23/01/2020	05/02/2020	4	13
27/01/2020	24/01/2020	27/01/2020	3	3
28/01/2020	25/01/2020	28/01/2020	3	3
29/01/2020	26/01/2020	31/01/2020	5	5
30/01/2020	27/01/2020	05/02/2020	4	9
31/01/2020	28/01/2020	05/02/2020	4	8

 

[msarfaraz]

result

System Date	Arrival	Departure	Stays Period	Total Nights
01/01/2020	29/12/2019	01/01/2020	0	3
02/01/2020	30/12/2019	02/01/2020	1	3
03/01/2020	31/12/2019	03/01/2020	2	3
04/01/2020	01/01/2020	04/01/2020	3	3
05/01/2020	02/01/2020	05/01/2020	3	3
06/01/2020	03/01/2020	06/01/2020	3	3
07/01/2020	04/01/2020	07/01/2020	3	3
08/01/2020	05/01/2020	08/01/2020	3	3
09/01/2020	06/01/2020	09/01/2020	3	3
10/01/2021	07/01/2020	10/01/2020	3	System > Departure
11/01/2020	08/01/2020	11/01/2020	3	3
12/01/2020	09/01/2020	12/01/2020	3	3
13/01/2020	10/01/2020	13/01/2020	3	3
14/01/2020	11/01/2020	14/01/2020	3	3
15/01/2020	12/01/2020	15/01/2020	3	3
16/01/2020	13/01/2020	16/01/2020	3	3
17/01/2020	14/01/2020	17/01/2020	3	3
18/01/2020	15/01/2020	18/01/2020	3	3
19/01/2020	16/01/2020	19/01/2020	3	3
20/01/2020	17/01/2020	20/01/2020	3	3
21/01/2020	18/01/2020	21/01/2020	3	3
22/01/2020	19/01/2020	22/01/2020	3	3
23/01/2020	20/01/2020	23/01/2020	3	3
24/01/2020	21/01/2020	24/01/2020	3	3
25/01/2020	22/01/2020	25/01/2020	3	3
26/01/2020	23/01/2020	05/02/2020	4	13
27/01/2020	24/01/2020	27/01/2020	3	3
28/01/2020	25/01/2020	28/01/2020	3	3
29/01/2020	26/01/2020	31/01/2020	5	5
30/01/2020	27/01/2020	05/02/2020	4	9
31/01/2020	28/01/2020	05/02/2020	4	8

Result seems to be ok, can it done in DAX please

 

[sandy666]

can it done in DAX please

I don't know, maybe someone else

 

[msarfaraz]

I don't know, maybe someone else

can you share me the excel formula and i will do it in Dax