cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Katjabpk Frequent Visitor
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.

CaptureTimeFilter.JPG

 

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.

CaptureStackedFilteredBySlicer.JPG

 

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. 

CaptureStackedWithYears.JPG

 

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 Smiley Happy

 

Kr. Katjabpk

4 REPLIES 4
Community Support Team
Community Support Team

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

@Katjabpk,

 

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 

Katjabpk Frequent Visitor
Frequent Visitor

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

@v-yuta-msft thank you for your advise.

 

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?

Katjabpk Frequent Visitor
Frequent Visitor

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

@v-yuta-msft thank you for your advise.

 

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
Community Support Team

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

@Katjabpk,

 

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

 

Regards,

Jimmy Tao