Sort by most recent 12 months

[j_b_pbi]

Hi all,

Hoping someone can help...

I've got a line chart with x axis showing most recent 12 months, created using a month as text column in the Date Table and sorting that by month number. The visual is then filtered for the most recent 12 calendar months.

I'd quite like to be able to add a trend line to one of the values but can't because the x axis is categorical and not continuous.

Is there any way to get both the continuous months and a trend line?

Thanks in advance.

JB

[v-alq-msft]

Hi, @j_b_pbi 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @j_b_pbi 

Based on your description, I created data to reproduce your scenario.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is a one-to-one relationship between two tables.

You may create a calculated column and a measure as below.

Calculated column:
Monthnum = MONTH('Calendar'[Date])

Measure:
IsDisplay = 
var _year = SELECTEDVALUE('Calendar'[Date].[Year])
var _month = SELECTEDVALUE('Calendar'[Monthnum])
return
IF(
    OR(
        _year = YEAR(TODAY())&&_month<MONTH(TODAY()),
        _year = YEAR(TODAY())-1&&_month>=MONTH(TODAY())
    ),
    1,
    0
)

Then you need to put the measure in the visual level filter to display the corresponding result.

Best Regards

Allan

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

[j_b_pbi]

Hi Allan @v-alq-msft ,

Thanks for your help.

I can't quite get the breakdown over months. Am I missing something in the hierachy?

Thanks

[j_b_pbi]

@v-alq-msft 

[amitchandak]

@j_b_pbi , Not sure I got. But if you have created a month year in text format

You have to create a month year sort column and mark it as the sort column

month year sort = format([Date],"YYYYMM")

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column