cancel
Showing results for
Did you mean:
Frequent Visitor

## Create a dynamic time period and use as axis in graph

Hi

I am new to DAX and Power Query. I need to create a stacked bar chart where the axis is time periods. It is easy to put in Year, Month, date etc., but I need to create a new time period to put into the axis in the graph, that changes based on the filtering option. Here is my example:

I have a time filter on my report, which the report viewer is able to change depending on what time period is relevant.

My problem is, that when I try to setup the graph, following issues arise:

Issue 1:

When I set up the stacked bar shart the filter only allows to show the composition between the two atributes for the given time period. I need to be able to compare this composition to the same time period last year. I try to correct for that in issue 2.

Issue 2:

If I use "Year" as axis slicer and switch off the interaction between the time period slicer, I get values for each year. But the problem is, that it does not follow the chosen time period in the filter.

My need is to set up the stacked bar chart with time periods to compare the composition of red vs. black.

I need to compare 3 periods:

1. The first time period needs to be the same as the one selected in the time filter. Name example: Current period

2. The next time period needs to be "same period last year" based on "Current period". Name example: Current period-1

3. The last time period needs to be "same period last year" based on "Current period-1". Name example: Current period-2

Is this possible?

Thank you in advance, it would be of great help if anyone knows how to do this

Kr. Katjabpk

4 REPLIES 4
Community Support Team

## Re: Create a dynamic time period and use as axis in graph

To be general, you may create three measures using DAX formula like pattern below:

```Measure1 =
CALCULATE (
aggregation,
FILTER ( table, table[year] = SELECTEDVALUE ( table[year] ) )
)```
```Measure2 =
CALCULATE (
aggregation,
FILTER ( table, table[year] = SELECTEDVALUE ( table[year] ) - 1 )
)```

```Measure3 =
CALCULATE (
aggregation,
FILTER ( table, table[year] = SELECTEDVALUE ( table[year] ) - 2 )
)```

Regards,

Jimmy Tao

Frequent Visitor

## Re: Create a dynamic time period and use as axis in graph

But unfortunately I have tried this. It does not solve my problem because if I create the three measures using DAX, the stacked bar chart will just show the total volume composition for the specific time intervals. Then I cannot see the composition of red and black (Red indicating attribute value #1 and Black indicating attribute value #2).

I need to see the Volume by Period and Attribute.

The different time periods therefore need to be placed in the axis rather than in the Value. I have tried to make a mock up of how I would like the visualisation to look like, see the link to the picture:

https://ibb.co/DwNQ170

I would like to know if it is possible to create periods like below and place them in the axis. This way I can just place my "Volume calculation in the [Values] field and then place my attributes in the [Legend] field.

Trailing periods:

T12 = Trailing 12 month

T12-1 = T12 same period last year

T12-2 = T12-1 same period last year

Does this make sense?

Frequent Visitor

## Re: Create a dynamic time period and use as axis in graph

But unfortunately I have tried this. It does not solve my problem because if I create the three measures using DAX, the stacked bar chart will just show the total volume composition for the specific time intervals. Then I cannot see the composition of red and black (Red indicating attribute value #1 and Black indicating attribute value #2).

I need to see the Volume by Period and Attribute.

The different time periods therefore need to be placed in the axis rather than in the Value. I have tried to make a mock up of how I would like the visualisation to look like, see the link to the picture:

https://ibb.co/DwNQ170

I would like to know if it is possible to create periods like below and place them in the axis. This way I can just place my "Volume calculation in the [Values] field and then place my attributes in the [Legend] field.

Trailing periods:

T12 = Trailing 12 month

T12-1 = T12 same period last year

T12-2 = T12-1 same period last year

Does this make sense?

Community Support Team

## Re: Create a dynamic time period and use as axis in graph

Could you please post some sample data or sample pbix file for further analysis?

Regards,

Jimmy Tao

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 304 members 3,220 guests
Recent signins: