DAX to count 12 Months Sum

[yybi123]

Hi All,

How can I get the total of 12 Months Volume including current month?
For example, the value required at Jan 2020 will be (sum of Feb-19 to Jan 2020)=33

Month	Volume	Value Required
Jan-19	1
Feb-19	2
Mar-19	1
Apr-19	2
May-19	2
Jun-19	3
Jul-19	4
Aug-19	5
Sep-19	6
Oct-19	4
Nov-19	2
Dec-19	1
Jan-20	1	33
Feb-20	2	33
Mar-20	3	35
Apr-20	1	34

Thanks!

[yybi123]

Thanks @Greg_Deckler  , it works.

How can I then display the calculated measure in a line graph that only show last 12 months on X-Axis? I tried filter the visual with relative date for Month in last 12 months then it affected the calculated measure value.

For example, the calculated measure number for Jan 2020 is 33 but when I applied the visual filter, the value will be changed.

@amitchandak , i tried your measure but it only gives me the value of the month. Ie Jan 2020 will be 1 it does not sum up from Feb2019 to Jan2020 value.

[amitchandak]

@yybi123 , with a date table.

example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Greg_Deckler · Accepted solution]

@yybi123 Try:

Measure = 
  VAR __Max = MAX('Table'[Month])
  VAR __Min = EOMONTH(__Max,-12)+1
RETURN
  SUMX(FILTER(ALL('Table'),[Month]>=__Min && [Month]<=__Max),[Volumn])