cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Twister8
Helper II
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:

 

= 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

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

View solution in original post

6 REPLIES 6
sprather
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....

v-shex-msft
Community Support
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.

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)

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
Community Champion
Community Champion

You can check it out at HERE.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Dev Camp Session 22 768x460.jpg

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