Creating an open Account Measure using DAX

Thank goodness for the amazing people on the Power BI Forums helping with this. I have the solution but what I wanted to do was to create a post to explain the why

The Problem

I have accounts and events occurring on each account. What I want to know is the time period they are open

Just for this example I have taken One account with 11 activities

If you run the visual against start date all you get is a measure againt the start time period.

We are more interested in creating a measure to show how long the activity went on for.

For this example I am going to choose Jan 28 2017 to work with to continue because the end date for this is March 21st 2017 so this activity should span 3 months

The Date Dimension

To do this you must have a date dimension. For the purposes of this example I’m going to create one in DAX (Create table) Using the following:

Notice that I have used Start and End Dates to set the min and Max years. You should already have a date dimension to work with though.

Set up the relationship between the Fact table and Date dim

Start date is the Active join and End Date is the Inactive Join

You could create a role playing dimension for the End date and have that as an active join but in this case I’m going to go for the above design.

Account Start flag (New Measure)

• CALCULATE – You can modify your expression using a filter
• ALLSELECTED – If you have a slicer set to Year (Date) , ALLSELECTED takes the ‘Context’ from the slicer Like 2018.
• Date is less than or equal to the Max Date in the Filter Context

Account End Flag (New Measure)

• ENDOFMONTH – Returns the last date of the month in the current context for the specified column of dates.
• The date at the end of the month is less than the Max date in the Filter Context
• USERELATIONSHIP Because End Date is the inactive relationship we need to specify that this relationship is used, Not Start Date

Add an Open Measure

To understand the results we need to try and understand how the new measures work

Jan

• Start Flag =        1 because 18^th Jan is less than Jan 31st
• End Flag  =        NULL because March 21^st (Reset to March 31^st) is NOT Less than Jan 31^st
• Open =  1- null = 1

Feb

• Start Flag =        1 because 18^th Jan is less than Feb 28^th
• End Flag  =        NULL because March 21^st (Reset to March 31^st) is NOT Less than Feb 28th
• Open =  1- null = 1

Mar

• Start Flag =        1 because 18^th Jan is less than Mar 31st
• End Flag  =        NULL because March 21^st (Reset to March 31^st) is NOT Less than March 31st
• Open =  1- null = 1

Apr

• Start Flag =        1 because 18^th Jan is less than April 30th
• End Flag  =        1 because March 21^st (Reset to March 31^st) is Less than April 31st
• Open =  1- 1 = 0

There are usually lots of ways to answer one question so this is one of many but the Open Flag now allows me to pull up all my activities and show in a Matrix where the are open.