Forum Discussion

julie_tai's avatar
julie_tai
Frequent Visitor
7 years ago
Solved

Create a dynamic reference line for tracking 12 months

As a beginner, I searched lots of articles, and it worked out with showing the visual data for tracking 12 months. Now there is another problem, which is, I would like to use the last-month average ...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi ryan_mayu

     

    Try below measure:

    Measure = 
VAR CurrentDate =
    MAX ( 'Date-2'[Date] )
VAR PreviousDate =
    DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, 1 )
RETURN
    IF (
        MAX ( 'Date'[Date] ) >= PreviousDate
            && MAX ( 'Date'[Date] ) <= CurrentDate,
        CALCULATE (
            [Demand Charge per Train] + [Energy Charge per Train],
            FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = CurrentDate )
        )
    )

    Regards,

    Cherie

ryan_mayu's avatar
ryan_mayu
Super User
7 years ago

v-cherch-msft

 

My sample data is a little bit different from yours. I want to show sales as bar chart and the value of last month I selected as a line.

 

If I choose Jan, Feb, Mar, we will only see three bars and the line value is 300 for all three months.

 

Another questions is how to create a line value which is the average of the sales I selected.

 

For example, if I choose Jan, Feb, Mar and Apr, we will see 4 bars and line is the average of (100+200+300+400)

 

If I choose 12 months in 2018, we will see 12 bars and line value automatically changes to average of (100+200+....+1200)

 

Thanks in advance.

 

 

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi ryan_mayu

 

I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

 

Regards,

Cherie