Latest month calculation

[Anonymous]

Hi

I'm after some help with creating a "Latest month" measure which is slightly beating me.

I would like to create a card which shows a count of records based on the latest month available in a table of data. The issue is that the "Latest month" could be 2/3/4 months prior to the current month we are in.

Currently i have the following measure

Count_Latest_Month = CALCULATE(COUNT(Table[ID]),Table[Extract_date] = MAX(Table[Extract_date]))

This works to a certain extent, however my card has a filter on it. If the latest month of data does not have any records to match that filter, the measure automatically defers to the next where it does have data instead of providing a zero number.

For example, on the data below i want to filter for the latest month and the count of records which are "No". The measure above gives me 10 from Dec-21 instead of the zero from Jan-22 which i require

Date	Yes	No
Jan-22	10	0
Dec-21	20	10

I appreciate this can easily be done in table/matrix visuals but for the purposes of my dashboard i'd like to show it in a card

Does anyone have any solutions??

Many thanks

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

If  the date filter is based on the current date, please try:

Last date based on Today = 
var _date=MAXX(FILTER('Table',[Date]<TODAY()),[Date])
return CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=_date)) 

Or if you want a dynamic date filter, please add a calendar table firstly. For example:

Date Filter = CALENDAR(MIN('Table'[Date]),TODAY()) 

Then 

Last date based on date filter = 
var _last= MAXX(FILTER('Table',[Date]<MAX('Date Filter'[Date])),[Date])
return CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=_last)) 

Or based on the previous month of date filter:

Last month based on date filter = 
var _last= MAXX(FILTER('Table',[Date]<MAX('Date Filter'[Date]) && YEAR([Date])*100+MONTH([Date])< YEAR(MAX('Date Filter'[Date]))*100 + MONTH(MAX('Date Filter'[Date]))),[Date])
return CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]=_last))

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.

[Anonymous]

Thanks for the suggestions. I got around it in the end by creating a calculated column which gives a marker for the latest month, and then just filtered the visual for "1"

LatestMonth = if(Table[Extract_date] = max(Table[Extract_date]), 1,0)

[Anonymous]

@amitchandak thanks for the response but unfortunately it still gives the same outcome

[amitchandak]

@Anonymous , Try a measure like

CALCULATE([sales],TOPN(1,allselected(Date[Month year]),calculate(Max('Date'[Date]), filter(values(Date[Month year]),[sales] <>0 )) ,DESC),VALUES(Date[Month year]))