cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gavin254
Frequent Visitor

Latest month calculation

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
 
DateYesNo
Jan-22100
Dec-212010

 

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

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @Gavin254 ,

 

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

Eyelyn9_0-1649129530935.png

 

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

Eyelyn9_4-1649129952343.png

 

 

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

Eyelyn9_3-1649129931035.png

 

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.

View solution in original post

5 REPLIES 5
Eyelyn9
Community Support
Community Support

Hi @Gavin254 ,

 

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

Eyelyn9
Community Support
Community Support

Hi @Gavin254 ,

 

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

Eyelyn9_0-1649129530935.png

 

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

Eyelyn9_4-1649129952343.png

 

 

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

Eyelyn9_3-1649129931035.png

 

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.

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)
Gavin254
Frequent Visitor

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

amitchandak
Super User
Super User

@Gavin254 , 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]))

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.