cancel
Showing results for
Did you mean:
Helper II

## Show total only last month

Hi,

I need help

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

When I use the expression:

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

1 ACCEPTED SOLUTION
Helper II

HI,

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)

6 REPLIES 6
Frequent Visitor

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

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper II

HI,

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)

Frequent Visitor

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

Community Champion

You can check it out at HERE.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Helper II

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.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors