topic Re: Show total only last month in Developer

Show total only last month

Twister8 — Thu, 10 Nov 2016 20:20:52 GMT

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

So I need return the 20 in my result

Re: Show total only last month

BhaveshPatel — Thu, 10 Nov 2016 20:41:46 GMT

You can check it out at HERE.

Re: Show total only last month

Twister8 — Fri, 11 Nov 2016 00:21:29 GMT

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.

Re: Show total only last month

Anonymous — Mon, 14 Nov 2016 07:49:29 GMT

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

Re: Show total only last month

Twister8 — Wed, 16 Nov 2016 16:32:30 GMT

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)

Re: Show total only last month

alanhanssen — Thu, 05 Jan 2017 15:04:52 GMT

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())

Re: Show total only last month

sprather — Tue, 08 Aug 2017 15:40:46 GMT

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....