Rolling 6 month Open Contracts in Power BI

2
votes

I need to show how many active contracts we have open for each month in the last 6 months. I am trying to figure out a way to display this. Here is my table

Machine Enrollment# StartDate   EndDate
A       1           1/2/2016    6/18/2019
B       2           12/15/2012  5/12/2034
C       3           3/25/2019   4/25/2021
D       4           1/7/2000    7/15/2019
A       5           10/1/2019   10/1/2025

I have thousands of rows. I want to be able to show a rolling 6 month visual for how many machines are under contract. So in this small example it would look like this

Apr-19  June-19  Jul-19  Aug-19  Sep-19  Oct-19
4       4        3       2       2       3

Where do I even begin in creating this? In the past, we have just looked at the numbers for the current month and tacked those results onto the end of a static table and deleted the column from over 6 months ago. I have been assigned to automate this report in Power BI. I am guessing I need to create a column/measure that looks at the EndDate and compares it to the filtered Date in the visual (ie: Aug-19) and determines if the contract was open at that time. But I do not know. Any help is much appreciated. Thanks in advance!

2
powerbidax
Is the number of Oct 19 correct? If I was understanding correctly, the open contracts in Oct 19 should be B and C. – Kosuke Sakai
Sorry, I had the start date for Machine A Enrollment 5 in November. I changed it to October to show that a Machine could go off contract for a month or two and then come back on contract – Conner

2 Answers

0
votes

I think I found a solution for what you are looking for. You may find a sample pbix file here.

1. Create a calendar table

A calendar table is required to filter/slice the time periods. The calendar table needs to have a unique Date column, and optional columns such as Year, Quarter, and Month, depending on what units of period you need in the analysis.

A calendar table can be most easily created as a DAX calculated table. Here is an example of a minimal calendar table required in this use case.

Calendar = 
ADDCOLUMNS(
    CALENDAR( MIN( Contracts[StartDate] ), MAX( Contracts[EndDate] ) ),
    "Year Month", FORMAT( [Date], "mmm-yy" ),
    "Year Month Number", YEAR( [Date] ) * 100 + MONTH( [Date] )
)

2. Create a measure to calculate number of open contracts

Every numbers calculated and shown in reports need to be defined as measures.

Let's think about the number of May-19. The current filter context includes all 31 dates in the Calendar table between 2019-05-01 and 2019-05-31. In this case, how can we think of an open contract? If the contract starts after 2019-05-31, it is not open. If the contract ends before 2019-05-01, it is not open as well. Therefore the open contract meets this condition.

  • Starts on or before 2019-05-31 and
  • Ends on or after 2019-05-01

Below is the measure definition to count the number of contracts based on this condition.

# Open Contracts = 
VAR MinDate = MIN( 'Calendar'[Date] )
VAR MaxDate = MAX( 'Calendar'[Date] )
RETURN COUNTROWS(
    FILTER(
        Contracts,
        Contracts[StartDate] <= MaxDate
            && Contracts[EndDate] >= MinDate
    )
)

3. Add dynamic filter for last 6 months

If I was understanding correctly, the requirement is to show monthly number of last 6 calendar months, excluding this month. I could not find a straightforward way for this. My solution may contain a bit of hacky scent.

Power BI does not have built-in filter support based on calendar months relative to now. We need to build a custom logic to achieve this. I did it by creating a measure that indicates whether current filter context is within the desired period. This measure is a flag that returns 1 if the filter context is a single calendar month which is included in the last 6 calendar months, or returns BLANK otherwise.

__Last6MonthFlag = 
VAR YearMonths = CALCULATETABLE(
    VALUES( 'Calendar'[Year Month] ),
    REMOVEFILTERS( 'Calendar'[Year Month] ),
    'Calendar'[Date] > EOMONTH( TODAY(), -7 )
        && 'Calendar'[Date] <= EOMONTH( TODAY(), -1 )
)
RETURN IF(
    HASONEVALUE( 'Calendar'[Year Month] )
        && SELECTEDVALUE( 'Calendar'[Year Month] ) IN YearMonths,
    1
)

Then I used this measure in the visual filter like this.

0
votes

You need to do below activities to achieve your requirement.

  1. Define a calendar table holding all the dates for your report. Define a calculated column for Month-Year. Month-Year = FORMAT('CalendarTable'[Date], "MM-YYYY")

  2. You can define a calculated measure, which will return 1 if the end date of the contract is < 6 months from current date (you can use TODAY() function). This function will help in rolling calculation based on current date. Otherwise, this calculated measure will return NULL and SUM them.

  3. You can drag the month-Year calculated column, defined in step no. 1, to the column axis. You can drag the calculated measure, defined in step no.2, to the values section. PowerBI control by default filters out the NULL values. So, when you use the calculated measure defined in step no. 2, you will get values only for the last 6 months. As the calculation is defined based on TODAY(), it will be a running calculation.