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]

this is not formula but Power Query
Power BI contain Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DA = Table.AddColumn(Source, "DA", each Duration.Days([Departure] - [Arrival]), Int64.Type),
    DS = Table.AddColumn(DA, "DS", each Duration.Days([Departure] - [System Date]), Int64.Type),
    Nights = Table.AddColumn(DS, "Total Nights", each if [System Date] <= [Arrival] then [DS] else if [Departure] < [System Date] then "System > Departure" else [DA]),
    DSOD = Table.AddColumn(Nights, "DSOD", each Date.StartOfMonth([Departure])),
    StaysPeriod = Table.AddColumn(DSOD, "Stays Period", each if Date.Month([Departure]) = Date.Month([Arrival]) then [DA] else [Departure] - [DSOD]),
    TSC = Table.SelectColumns(StaysPeriod,{"System Date", "Arrival", "Departure", "Stays Period", "Total Nights"}),
    Type = Table.TransformColumnTypes(TSC,{{"System Date", type date}, {"Arrival", type date}, {"Departure", type date}, {"Stays Period", Int64.Type}})
in
    Type

 

[KRice]

This is an interesting problem from a general mathematical perspective, but I'm wondering about some constraints:
Is the System Date always >= Departure Date?

 

[sandy666]

Is the System Date always >= Departure Date?

who said that?

 

[KRice]

If this were a general problem, then the System Date could occur at any time relative to the Arrival Date (A), or at any time relative to the Departure Date (D), but since the "system" appears to have a scheme for determining the number of nights stayed during the month represented by the System Date (S), this suggests that the system must "know" A and D, therefore I would assume the sequence must be A<=D<=S
This wasn't explicitly stated, but based on the specific application here, I think it necessarily follows. ..and the formula sought changes depending on whether this is true.

 

[KRice]

Depending on the intended purpose of this tool, it is conceivable that it could be used for retrospective analysis, e.g., an audit to evaluate end-of-month status for some month that occurred in the distant past. In that case, the System Date (or point-in-time for the night's stay determination) could be set to a date earlier than D, and my question about whether A<=D<=S has the answer, "no, not always true". Now that I look more closely at the image posted by the OP, I see an example where this is confirmed.

 

[sandy666]

@KRice
S has some meaning here as it appears in the table.
It might as well be done only on A and D but with other conditions determining the way of counting nights, Total or/and in the same month as D.
You have to wait for the OP answer because it is the only person here who knows anything more than we do.

 

[KRice]

If you are still interested, here is further analysis of the question posed:

We consider a guest's stay somewhere and our objective is to determine how many nights of the stay occurred during the month associated with an arbitrary System Date, S. The guest's stay is described in terms of an Arrival Date, A, and a Departure Date, D.

Logical Constraints:
Time is resolved to the day, so generally A<=D, otherwise a "time's arrow error" (TAE) is introduced because arrivals must precede departures. The special case where A=D is considered valid because of the time resolution limit.

Since the objective depends on the months associated with the dates, the beginning of month (bm) and end of month (em) associated with each date may be important, and nomenclature is adopted such that bm or em is prefixed to the date variable, e.g., emS is the end-of-month date associated with S. For any part of the guest's stay to occur during the month represented by S, both of these constraints must be true: S>=bmA AND S<=emD.

• If the first were not true, then S would occur in some month prior to the month when the guest arrives, and no part of the guest's stay would occur during the month associated with S. So the answer is 0 due to S<bmA.
• If the second were not true, then S would occur in some month after the month when the guest departs, and no part of the guest's stay would occur during the month associated with S. So the answer is 0 due to S>emD.

Analysis:
There are six ways to arrange three dates in sequence: {ADS; ASD; SAD; SDA; DAS; DSA}. The latter three are not valid due to a TAE. Each of the first three valid sequences can overlay a monthly calendar in multiple ways. Many of those arrangements result in 0 for the "number of days stayed in S month", NDS, due to the logical constraints described above. For those cases where the logical constraints do not result in 0, the following expression establishes the objective quantity:
(D-A)-IF(D>emS,(D-emS)-1,0)-IF(A<bmS,bmS-A,0)

• The (D-A) term represents the number of day intervals between the two dates, and thus already considers that the night of the departure day does not count as a night stayed, so no correction term is needed.
• The (D-emS) term represents the number of day intervals between the departure date and the end of the System Date month, yet the last night of the System Date month would count as a night stayed, therefore this difference is 1 day too large and a correction term of -1 is needed.
• The (bmS-A) term does not require this same correction because the night leading into bmS is assigned to the day bmS-1, thus the number of day intervals is correct.

Assembling all of this into a single formula: NDS = IF(A>D,"TAE",IF(OR(S<bmA,S>emD),0,(D-A)-IF(D>emS,D-emS-1,0)-IF(A<bmS,bmS-A,0)))

Excel formulae for bm and em quantities in expression above:
bmA=EOMONTH(A,-1)+1
bmS=EOMONTH(S,-1)+1
emD=EOMONTH(D,0)
emS=EOMONTH(S,0)

Substitution to express in terms of Excel functions and date variables:
NDS=IF(A>D,"TAE",IF(OR(S<(EOMONTH(A,-1)+1),S>EOMONTH(D,0)),0,(D-A)-IF(D>EOMONTH(S,0),D-EOMONTH(S,0)-1,0)-IF(A<(EOMONTH(S,-1)+1),(EOMONTH(S,-1)+1)-A,0)))

MrExcel_2.xlsm
	A	B	C	D	E	F
1	System Date (mm/dd/yyyy)	Arrival (mm/dd/yyyy)	Departure (mm/dd/yyyy)	Nights Stayed in month of System Date	Total Nights Stayed	Notes about terms invoked in formula
2	01/26/2020	01/23/2020	02/05/2020	9	13
3	01/31/2020	01/28/2020	02/05/2020	4	8	D>emS
4	01/31/2020	01/28/2020	01/27/2020	TAE	TAE	time's arrow error
5	01/15/2020	02/13/2020	02/17/2020	0	4	S<bmA
6	03/15/2020	02/13/2020	02/17/2020	0	4	S>emD
7	01/18/2020	01/15/2020	01/19/2020	4	4	simple (D-A)
8	01/18/2020	01/28/2020	02/05/2020	4	8	D>emS
9	02/03/2020	01/28/2020	02/05/2020	4	8	A<bmS
10	01/01/2020	12/29/2019	01/01/2020	0	3
11	01/12/2020	12/31/2019	01/05/2020	4	5
12	01/28/2020	12/25/2019	01/10/2020	9	16
Sheet17

Cell Formulas
Range	Formula
D2:D12	D2	=IF(B2>C2,"TAE",IF(OR(A2<(EOMONTH(B2,-1)+1),A2>EOMONTH(C2,0)),0,(C2-B2)-IF(C2>EOMONTH(A2,0),C2-EOMONTH(A2,0)-1,0)-IF(B2<(EOMONTH(A2,-1)+1),(EOMONTH(A2,-1)+1)-B2,0)))
E2:E12	E2	=IF(B2<=C2,C2-B2,"TAE")

 

[sandy666]

you probably haven't noticed that this is a PowerBI forum
worksheet functions/formulas doesn't work in PowerBI, PowerQQuery & PowerPivot

 

[KRice]

Of course, you're right. I forgot to mention that my offering didn't address the original request for a resolution in DAX, but I thought the formula development might help someone implementing a solution in DAX.

 

[sandy666]

sure, have a nice day