Formula To Retrieve Data from Last Year Month In Same Column

[Anonymous]

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 

[v-yulgu-msft]

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]

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. 

[v-yulgu-msft]

Hi @Anonymous,

You could apply a visual level filter to the chart. 

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana,

I dont seem to have the option of relative date filtering?

[v-yulgu-msft]

Hi @Anonymous,

Select the chart visual, what can you see in the drop down list?

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana 

The options i have are as below

[v-yulgu-msft]

Hi @Anonymous,

Please make sure the [date] field added to visual is formatted as date, rather than date hierarchy. Please check the difference from below screenshots.

             

    

Best regards,

Yuliana Gu

[themistoklis]

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