How to combine values on the X-Axis

[BaptisteAnge]

Hi everyone,

Consider this table (simplified for a better explanation) :
MyEntities:

Year_Opening	Year_Closing	State
2012	null	Open
2015	null	Open
2014	null	Open
2014	2016	Close
2013	2015	Close
2015	null	Open
2016	2017	Close

As you can guess, Year_Opening is the year when the entity opened and cannot be null, Year_Closing is the year when the entity closed (if it closed) and can be null, and State is equal to "Open" if Year_Closing is null and to "Close" if there is a value in Year_Closing.

I was to asked to do a reporting that shows for every year the number of entities that has opened and closed and then the cumulative value, so I created this measures :

Count_Open = 
COUNTX(
    FILTER(
        MyEntities,
        MyEntities[State] = "Open"
    ),
    MyEntities[State]
)

Count_Close = 
COUNTX(
    FILTER(
        MyEntities,
        MyEntities[State] = "Close"
    ),
    MyEntities[State]
)

Cumul_Open =
CALCULATE(
    [Count_Open],
    FILTER(
        ALLSELECTED(MyEntities[Year_Opening]),
        MyEntities[Year_Opening] <= MAX(MyEntities[Year_Opening])
    )
)

Cumul_Close =
CALCULATE(
    [Count_Close],
    FILTER(
        ALLSELECTED(MyEntities[Year_Closing]),
        MyEntities[Year_Closing] <= MAX(MyEntities[Year_Closing])
    )
)

My problem is:

I want to display those two by two (counts together and cumuls together) in two separate charts, but when I add Year_Opening and Year_Closing to the x-axis, instead of combine (using my example having a scale from 2012 to 2017), the values of both follows each other (so my scale on x-axis looks like (2012, ..., 2015, 2016, 2015, 2016, 2017).

Thank for your help.

[123abc]

It seems like you want to display the counts and cumulative values of entities that opened and closed over the years on separate charts while having a consistent and combined X-axis scale. To achieve this in Power BI, you can use a few calculated tables and DAX measures.

Create two separate tables for Year_Opening and Year_Closing:

Create a new table for Year_Opening:

Year_Opening_Table =
UNION(
SELECTCOLUMNS(MyEntities, "Year", MyEntities[Year_Opening]),
SELECTCOLUMNS(MyEntities, "Year", MyEntities[Year_Closing])
)

1. Create a relationship between Year_Opening_Table and MyEntities using the "Year" column in both tables.

2. Create a "Type" column in Year_Opening_Table to distinguish between "Open" and "Close" years. You can use the following DAX formula:

Type = IF(ISBLANK([Year]), "Close", "Open")

1. Create two measures for Count and Cumulative values for "Open" and "Close" separately:

   Count_Open = COUNTROWS( FILTER( Year_Opening_Table, Year_Opening_Table[Type] = "Open" ) )

   Count_Close = COUNTROWS( FILTER( Year_Opening_Table, Year_Opening_Table[Type] = "Close" ) )

   Cumul_Open = CALCULATE( [Count_Open], FILTER( ALLSELECTED(Year_Opening_Table[Year]), Year_Opening_Table[Year] <= MAX(Year_Opening_Table[Year]) ), Year_Opening_Table[Type] = "Open" )

   Cumul_Close = CALCULATE( [Count_Close], FILTER( ALLSELECTED(Year_Opening_Table[Year]), Year_Opening_Table[Year] <= MAX(Year_Opening_Table[Year]) ), Year_Opening_Table[Type] = "Close" )

2. Create two separate charts for "Open" and "Close" counts and cumulative values. Use the "Year" column from the Year_Opening_Table for the X-axis in both charts.

Now, when you add Year_Opening and Year_Closing to the X-axis, they should follow a consistent combined scale from 2012 to 2017, and the values for "Open" and "Close" will be displayed separately in their respective charts.

By creating a Year_Opening_Table and using the "Type" column, you can ensure that the X-axis scale remains consistent across both charts while allowing you to filter and display counts and cumulative values separately based on the "Open" and "Close" states.

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

[BaptisteAnge · Accepted solution]

Your solution didn't work, but I managed to do something.

I combined the two year columns into one, and added a calculated "Event" column. So, after transformation, the table (if I take the same as my first example) looks like this:

Year	Event
2012	Opening
2015	Opening
2014	Opening
2014	Opening
2013	Opening
2015	Opening
2016	Opening
2015	Closing
2016	Closing
2017	Closing

We can then easily measure the number of openings and closings, and deduce the number of open entities by doing something like number_Open = [number_Opening] - [number_Closing], and we have now a single column to put on the X-Axis.