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

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

8 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    7 years ago

    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's avatar
      v-cherch-msft
      Microsoft Employee
      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.

         

         

  • julie_tai's avatar
    julie_tai
    Frequent Visitor
    7 years ago

    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

     

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

      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