topic Re: creating moving measure of the last 12 months without accumulating. in DAX Commands and Tips

creating moving measure of the last 12 months without accumulating.

David_Marks — Thu, 05 Aug 2021 18:35:36 GMT

I need a measure that returns me the last 12 months of my base, but without accumulating. For example, if the base have 16 months, it only returns the last 12 months. it would be another moving measure always bringing the last 12 months. But if in my base have just 5 months will bring me just 5 months and when i have 12 months or more, it will bring me 12 months always 12 last months.

Re: creating moving measure of the last 12 months without accumulating.

amitchandak — Fri, 06 Aug 2021 06:03:10 GMT

David_Marks , Based on what I got,

First, create a measure

Avg 1 = averageX(Values('Date'[Month Year]), calculate(SUM(Sales[Sales Amount])))

then try

Cumm Sales = CALCULATE([Avg 1],filter(allselected(date),date[date] <=max(date[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: creating moving measure of the last 12 months without accumulating.

David_Marks — Fri, 06 Aug 2021 14:47:00 GMT

i created this measure and it worked for me i'm posting here to help other people with the same problem, but thank you for all the help.

CALCULATE(

[Valor Consumo],

DATESINPERIOD(

dData[Data],

LASTDATE(dData[Data]),

-12,

MONTH),

VALUES(dData[Nome do Mês])

)