How do I get the latest metric value available in a metric table, ordered by a calendar table?

[ali_b]

Hi,

Going mad here. I have a simple model with a calendar (days granularity), and metric table (one row per metric / day), joined on a one to many with cross filtering in both directions (joined on Date column).

The metric table looks like this:
"date",  "metric value"

d1, 1

d3, 2

The calendar table has values d0 to d10 or whatever (no other columns).

I want to create a measure (not a table, not m code, a measure) which produces the latest available metric value. So for five days it would look like:
d0, null,

d1, 1,

d2, 1,

d3, 2,

d4, 2,

...

d1000, 2

Closest I've gotten is:

Measure =

    var t = FILTER(all(Metrics), RELATED('Calendar'[Date]) <= Metrics[Date])

    var v = TOPN(1, t, Metrics[Date], DESC)    

    VAR x = maxx(v, Metrics[Goal])

    return x

But this produces:
d0,2

d1,2

d2,2

d3,2

d4,2

...

So it's not actually filtering on the calendar date range.

Any ideas?

Thanks in advance!

A

[v-echaithra]

Hi @ali_b ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @ali_b ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[HarishKM]

@ali_b Hey,
I will  use this measure that retrieves the latest available metric value relative to each date in your calendar, you'll need to ensure the measure respects the date context provided by your calendar table.

LatestMetricValue =
VAR LatestMetricDate =
CALCULATE (
MAX ( Metrics[Date] ),
FILTER ( ALL ( Metrics ), Metrics[Date] <= MAX ( 'Calendar'[Date] ) )
)
RETURN
CALCULATE ( MAX ( Metrics[MetricValue] ), Metrics[Date] = LatestMetricDate )

Note: This measure efficiently filters and respects the context of each date in your calendar.

Thanks

Harish M

Kudos will be appriciated and accpet it as solution if it solves your problem

[v-echaithra]

Hi @ali_b ,

Thank you @Greg_Deckler , @ryan_mayu  for your inputs.

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[ryan_mayu · Accepted solution]

@ali_b 

you can try this. 

Do not create relationships between two table

then create a measure

Measure =

var _date=maxx(FILTER('Table','Table'[Date]<=max('date'[Date])),'Table'[Date])

return if(ISBLANK(_date),"null",maxx(FILTER('Table','Table'[Date]=_date),'Table'[metric value]))

pls see the attachment below

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Greg_Deckler]

@ali_b Should be something like this:

Measure =
  VAR __Day = MAX( 'Calendar'[Date] ) // assuming using Date from Calendar in visual
  VAR __MetricDate = MAXX( FILTER( ALL( 'Metrics' ), [Date] <= __Day ), [Date] )
  VAR __Result = MAXX( FILTER( ALL( 'Metrics' ), [Date] = __MetricDate )
RETURN
  __Result