Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Formula To Retrieve Data from Last Year Month In Same Column

Hi 

 

I currently have a clustered column chart as follows. 

 

 

 

 

 

 

 

 

- The measure used has data dating back to 2016. 

- The green bar is Last year figure ie July 2017 August 2017 etc and the blue bar is this years figure. 

- The database still has not yet been fed through the data for this year for November 2018 or December 18 or so forth. 

 

Is there anyway to create something/measure/column so that even though the data for this year hasnt fed through it could still show last years figure?

 

The figure would have to be retreived from the same column. In other words the columns of data are as follows (Eg Numbers). As you can see from the table below the data for nov-18 still hasnt shown up. However I still want the graph to display LY figure from this column 

 

 

Need Help

8 Replies

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

    Hi Anonymous,

     

    Please link the data table to a date dimention table. Add [Date] field from date dimention table onto chart X-axis, rather than from data table. Then, to calculate the value of previous year, please try below measure:

    LY = CALCULATE(SUM(DataTable[Value]),SAMEPERIODLASTYEAR('dim date'[Date]))
    Best regards,
    Yuliana Gu
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Yuliana,

       

      Thanks! This did half the job

       

      I would want the figure to show up under the November 2018 section and not show up in the November 2017 section?

       

      If that makes any sense. 

      • themistoklis's avatar
        themistoklis
        Community Champion
        7 years ago

        Anonymous

         

        Can you try this:

        Total Sales LY = 
VAR MaxDate = EDATE(CALCULATE(MAX(TODAY()), ALL(TableName)),-12)
RETURN
CALCULATE(
    SUM(TableName[Value]),
    FILTER(SAMEPERIODLASTYEAR('TableName'[Date]),'TableName'[Date]<=MaxDate)  
)