Graph with different months

[nuriac]

Hello

I want to make a chart where the axis shows me up to three more months than I've selected.

That is, if I select September, it shows me values before September and also October, November and December.

how can I do that?

[v-zhenbw-msft]

Hi @nuriac ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft · Accepted solution]

Hi @nuriac ,

When you select the December, do you want to show the January and February in the current year or in the next year?

If you want to show the January and February in 2021 when you select the December 2020, you can refer the following steps.

The process of creating tables is the same as the previous reply.

1. We need to add to columns in date table and table seperately. The year month column and the year month value column.

Year_month = FORMAT('Table'[Date],"mmmm") &"-"& YEAR('Table'[Date])

Year_month_value = YEAR('Table'[Date])*100 + MONTH('Table'[Date])

2. Then we can create a measure to get the result.

Measure = 
VAR _select =
    SELECTEDVALUE ( 'Date table'[year_month] )
VAR _current_month =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year_month] = _select )
    )
VAR _current_month_num =
    CALCULATE (
        MAX ( 'Table'[Year_month_value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year_month] = _select )
    )
VAR _threemonths_after =
    EDATE ( _current_month, +3 )
VAR _threemonths_value =
    CALCULATE (
        MAX ( 'Table'[Year_month_value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _threemonths_after )
    )
RETURN
    IF (
        ISFILTERED ( 'Date table'[year_month] ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Year_month_value] >= _current_month_num
                    && 'Table'[Year_month_value] < _threemonths_value
            )
        ),
        SUM ( 'Table'[Value] )
    )

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[v-zhenbw-msft]

Hi @nuriac ,

We can create a month table and a measure to meet your requirement.

( Note: you need to create the month number column in your original table. )

1. Create a month table that has no relationship.

Table 2 = SUMMARIZE(ADDCOLUMNS(CALENDAR("2020/1/1","2020/12/31"),"Month_name",FORMAT([Date],"mmmm"),"Month_num",MONTH([Date])),[Month_name],[Month_num])

2. Then we can create a measure.

Measure = 
var _select = SELECTEDVALUE('Table 2'[Month_name])
var _current_month_num = CALCULATE(MAX('Table'[Month number]),FILTER(ALLSELECTED('Table'),'Table'[Month name]=_select))
var _threemonths_after = _current_month_num+3
return
IF(
    ISFILTERED('Table 2'[Month_name]),
    CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Month number]>=_current_month_num&&'Table'[Month number]<=_threemonths_after)),
    SUM('Table'[Value]))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[nuriac]

@v-zhenbw-msft

I've tried it but it doesn't work.

When I filter by December, I don't see January and February.

I've changed the calendar of the example you've set, but it doesn't work

[Fowmy]

@nuriac 

You can have another date table in your model and use it on the slicer for month selection and a measure could be created to calculate values as per your desired time span added to the graph.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[nuriac]

@Fowmy

ok.

How should I do that measure to only show up the months I want?