Show total only last month

[Twister8]

Hi,

I need help

I need show in my gauge the value just of the LASTMONTH

When I use the expression:

= CALCULATE([VALUE];DATEADD(Tempo[Date]; -1; MONTH))

My result its total, its sum all values in rows, but I need just the last month

In this example:

month      values

2016-1     1

2016-2     5

2016-10     10

2016-10     10

So I need return the 20 in my result

[sprather]

I just create a calculated column on the date field in the table:

IsPreviousMonthIns = IF(Month([Inspection_Date])=Month(Today())-1,"YES","NO")

Then set a visual level filter on this column to filter out the "No" results.

I also create a column to check current year:

IsCurrentYearIns = IF(Year([Inspection_Date])=Year(Today()),"YES","NO")

These two columns allow me to pull only last months results for the current year......Works nicely....

[Anonymous]

Hi @Twister8,

You can try to use below formula if it works on your side:

Total of last month =
var currDate=MAX(Tempo[Date])
return
SUMX(FILTER(ALL(Tempo),currDate>= DATE(YEAR(currDate),MONTH(currDate)-1,1)&&currDate<=DATE(YEAR(currDate),MONTH(currDate),1)-1),[VALUE])

Regards,

Xiaoxin Sheng

[Twister8 · Accepted solution]

HI,

Tks everybody for answers...

I solved:

Column

LastMonth = IF(YEAR(TODAY())= YEAR('Fact'[Date]) && MONTH(TODAY())-1 = MONTH('Fact'[Date]);1;BLANK())

Measure

Mymeasure = CALCULATE(SUM('Fact'[Value]);'Fact'[LatMonth] =1)

[alanhanssen]

Hi,

Your formula might have failed by now as the current year is not anymore the same as YEAR('Fact'[Date]) but still is last month.

I created a measure as the key for identifying

LastMonth = if(MONTH(TODAY())-1=0,12,MONTH(TODAY()-1)) &" "& (YEAR(TODAY())-1)

Also a column for identifying those.

IsInLastMonth = IF(DateTable[MonthAndYear]=[LastMonth],TRUE(),FALSE())

[BhaveshPatel]

You can check it out at HERE.

[Twister8]

Tks for answer 🙂

But i have a error:

A3 = IF(MONTH(LASTDATE(ALL('Fct'[DATA]))) = Time[Date]
&& YEAR(LASTDATE(ALL('Fct'[DATA]))) = Time[Date];"Y";"N")

Could not determine a single value for the 'Date' column in the 'Time' table. This can happen when a measure formula refers to a column containing many values, without specifying an aggregation, such as min, max, Cont or sound for a single result.