Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
    Anonymous
    Not applicable
    4 years ago

    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