topic Re: Value 12 Months Ago in DAX Commands and Tips

Value 12 Months Ago

DW868990 — Tue, 06 Sep 2022 11:47:48 GMT

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.

Re: Value 12 Months Ago

Adescrit — Tue, 06 Sep 2022 12:18:43 GMT

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.

Re: Value 12 Months Ago

DW868990 — Tue, 06 Sep 2022 12:24:49 GMT

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.

Re: Value 12 Months Ago

Adescrit — Tue, 06 Sep 2022 12:29:48 GMT

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?

Re: Value 12 Months Ago

DW868990 — Tue, 06 Sep 2022 12:37:15 GMT

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

Re: Value 12 Months Ago

Anonymous — Wed, 07 Sep 2022 02:50:18 GMT

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.

Re: Value 12 Months Ago

DW868990 — Wed, 07 Sep 2022 08:28:23 GMT

Thanks all I managed to resolve my using month offsets 🙂

Re: Value 12 Months Ago

Ekapusuz — Thu, 20 Mar 2025 06:35:50 GMT

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