Last 12 Months

[Anonymous]

Hi Experts

i cannot understand why i am getting constant on the previous 12 months DAX Measure when i add the measure to my calender table....to see the end results...

Rolling 12 till last month FEP =

VAR EndDatelastMonth =

EOMONTH(TODAY(),-1)

VAR StartDate =

DATE(YEAR(TODAY()),-12,1)

VAR Result =

CALCULATE(

[MONTHLY TOTAL (FEP)],

DATESBETWEEN( 'Calendar'[Date], StartDate, EndDatelastMonth )

)

RETURN

Result

[johnt75]

Change your start date to  DATE( YEAR(TODAY())-1, MONTH(TODAY()), 1)

[Anonymous]

My Start date should be start of the month 12 months ago....not the previous month??? unless i am missing something

[johnt75]

My formula is giving the start of the current month last year, so at the moment it is returning 2021-03-01. It is just subtracting 1 from the current year.

[Anonymous]

Ok but i am getting  constant value when i use Year and Month from the calenar table and plot this on a bar chart

[johnt75 · Accepted solution]

That is because the measure is not taking into account the calendar table, it is using fixed values. If you want to show the total value for the previous 12 months taking into account your calendar table you can use

Rolling 12 till last month FEP =
VAR EndDatelastMonth = MAX('Calendar'[Date])

VAR StartDate = DATE(YEAR(EndDatelastMonth) - 1,MONTH(EndDatelastMonth),1)

VAR Result = CALCULATE( [MONTHLY TOTAL (FEP)],
	REMOVEFILTERS('Calendar'),
	DATESBETWEEN( 'Calendar'[Date], StartDate, EndDatelastMonth ))
RETURN
Result