HOW TO GET THE LATEST VALUE FROM EACH MONTH FOR THE PAST 3 MONTHS

[Anonymous]

Hi Guys,

Newbie here,

Is it possible to get the latest VALUE for each month and show those VALUES for the past 3 months?

For example, today's month is APRIL, so I want to get the latest values for each of the last 3 months which are JAN, FEB, and MARCH.

Here is the sample dataset:

Here is the output I wanted to achieve:

Thank you so much for your input. Appreciate all the efforts.

[amitchandak · Accepted solution]

@Anonymous , new measure options

//Last 3 based on today
new measure =
var _max = eomonth(today(),0)
var _min = eomonth(today(),-3)+1
return
calculate( lastnonblankvalue('Date'[Date],sum(Table[Value])), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

If you want select from slicer then you need additional independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _min = eomonth(_max,-3)+1
return
calculate( lastnonblankvalue('Date'[Date],sum(Table[Value])), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

If the last 3 months is always based on  current date, actually slicer is not necessary. You could try the following formula to create a measure:

Measure = 
var _max=CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[User],'Table'[Date].[Year] ,'Table'[Date].[Month]))
var _value=CALCULATE(MAX('Table'[Value]),FILTER('Table',[User]=MAX('Table'[User]) && [Date]=_max))
return  IF( DATEDIFF(MAX('Table'[Date]),TODAY(),MONTH) in {1,2,3},_value,BLANK())

On my side, current date is 2022/2 , so the output will be in 2021/11, 2021/12 and 2022/1:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

The measure has to be written slightly different based on how your model looks like, but I hope you can get some ideas from the below.

Please check the below picture and the attached pbix file.

Value total: =
VAR selectedmonth =
MAX ( 'Calendar slicer table'[Month] )
RETURN
CALCULATE (
SUM ( Data[Value] ),
KEEPFILTERS (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Month] <= selectedmonth - 1
&& 'Calendar'[Month] >= selectedmonth - 3
)
)
)

[Anonymous]

Hi @Jihwan_Kim ,

Can you make it like the current month with no slicer? there is no need for the slicer. just set it to the current month. meaning past 3 months from the CURRENT month. And as I checked on your example, you've added all the values. Instead of that, is it possible to get only the latest value from each month?

So if you have 3 values in Employee A within January, lets say Jan 1 = 20 , Jan 2 = 30 you'll just get the value of Jan 2 which is 30 because it is the latest data of January.

Thank you so much!

[amitchandak · Accepted solution]

@Anonymous , new measure options

//Last 3 based on today
new measure =
var _max = eomonth(today(),0)
var _min = eomonth(today(),-3)+1
return
calculate( lastnonblankvalue('Date'[Date],sum(Table[Value])), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

If you want select from slicer then you need additional independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _min = eomonth(_max,-3)+1
return
calculate( lastnonblankvalue('Date'[Date],sum(Table[Value])), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI