Value 12 Months Ago

[DW868990]

Hi, 

I need to get a spot value of a measure from 12 months ago, but this needs to be a dynamic calculation.

I don't need the rolling/last 12 months cumulative figure, just spot value of the measure from 12 months ago.

I have a conventional date table in my model and am struggling with the DAX.

Any advice appreciated, thank you.

[Ekapusuz]

Hi how did you solve it because I'm also trying to do the same but all the formulas in DAX i tried, giving me blank or sum or fixed results..Thanks

[DW868990 · Accepted solution]

Thanks all I managed to resolve my using month offsets 🙂

[Anonymous]

Hi @DW868990 

Can you provide your sample and expected result through a pbix file(remove sensitive info) or a screenshot ? 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Adescrit]

Hi @DW868990 ,

12 Months Ago = 
    CALCULATE( [Measure to Calculate], DATEADD( 'Calendar'[Date], -1, YEAR ) )

You could try this, but need to replace the [Measure to Calculate] with whatever calculation you're trying to perform - it could be an existing measure or it could be a SUM / AVERAGE over a fact table column. 

[DW868990]

Thanks but this returns 12 data points, of the last 12 months, and i am looking for only 1 data point, but 12 months ago. 

[Adescrit]

How are you trying to present the data? Is it one data point in a card visual? A matrix of dates with their value from 12 months ago? Or a line chart in which you want to show for each date what the data point was from 12 months prior?

[DW868990]

a line shart with the data point of today, and only other data point showing being 12 months ago