Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
Solved! Go to 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.
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
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.
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"
@amitchandak thanks for the response but unfortunately it still gives the same outcome
@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]))