creating logic for open accounts?

[PA7177]

Hi,

I'm trying to create a formula(s) to calculate if an account is open, closed, or not yet opened during a Statement Month.

Statements open on the 6th and close on the 5th of the following month. e.g April 6 to May 5 is the April statement.

If an account is open during the statement month for at least 1 day, it is considered open.

If it is closed during the statement month, it is still considered open for that statement month, and considered closed the following month.

null close date = account not closed

Any ideas? Thanks in advance!

[v-yuta-msft · Accepted solution]

@PA7177 ,

Create a calculate column using dax below:

State = IF('Table'[Open Date1] > 'Table'[Statement End Date1] || 'Table'[Close Date1] < 'Table'[Statement Start Date1], "Closed", "Open")

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.