Create a dynamic reference line for tracking 12 months

[julie_tai]

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 value as the 12-month reference line.

Please help me.

This is what I expect.

 

【Original Files】

Energy Cost excel file

Power BI Desktop file

[julie_tai]

Thank all of your replies.

I made a little mistake about what I want. Please allow me to explain again.

The average line is a value of the last month.

The formula is as below.

[Trailing 12-month accumulative Energy Cost] / [Trailing 12-month Trains]

Take "Nov-2018" as example, the average value should be 28,016 dollars.

Originally, I tried to use LOOKUPVALUE, but it cannot be shown only between 2017-12 to 2018-11.

Anyway, I revised the pbix file.  I really need your hlep indeed.

Thank you.

PBIX file

[Ashish_Mathur]

Hi,

Have you tried my solution?

[v-cherch-msft]

Hi @julie_tai

Try below measure:

Measure = 
VAR CurrentDate =
    MAX ( 'Date(Irrelevant)'[Date] )
VAR PreviousDate =
    DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, 1 )
RETURN
    IF (
        MAX ( 'Date'[Date] ) >= PreviousDate
            && MAX ( 'Date'[Date] ) <= CurrentDate,
        CALCULATE (
            [TTM Average Energy Cost per Train],
            FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = CurrentDate )
        )
    )

Regards,

Cherie

[Ashish_Mathur]

Hi,

You may download my PBI solution file from here.

Hope this helps.

[ryan_mayu]

@julie_taiI

In this situation, I will have three tables.

1.Fact table (with month and value)

2.datetime table

3. Use DAX to create. (This will make sure the months are dynamic and you can always retrieve last month's value)

Table = VALUES(Sheet1[Month])

Then you build relationships among three tables.

Create a column in table 3 which you use DAX created to make sure the value in every month equals to the last month.

lastmonthaverage = 
VAR lastmonth=MAX('Sheet1'[Month])
return CALCULATE([average],FILTER(Sheet1,Sheet1[Month]=lastmonth))

At last, you can create the chart. Drag value in table 1 to the column value and Drag the new column you created to the line value.

[v-cherch-msft · Accepted solution]

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]

@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]

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