Forum Discussion

j_b_pbi's avatar
j_b_pbi
Frequent Visitor
6 years ago
Solved

Sort by most recent 12 months

 

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

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

5 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      j_b_pbi
      Frequent Visitor
      6 years ago

      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

       
  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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