Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

creating logic for open accounts?

 

 

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!

 

 

 

 

 

Need Help
Tutorial Requests
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    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.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ,

     

    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.