Return Latest Value in Column Based on Date

0
votes

I am still very new to Power Pivot & DAX Measures, so a little help would be greatly appreciated here!

I have the below table:

enter image description here

I am trying to build a DAX measure that will return the latest [Universe] value. I have tried a combination of CALCULATE, MAX, FILTER, EARLIER, but am struggling to get the desired output, as seen below.

enter image description here

I don't necessarily want the outputs in a table, but rather in a measure because I want to use it in a calculation.

Note, this is my current attempt:

Total Universe:=SUM([Universe])

Last Universe Date:=CALCULATE(MAX([Date]),FILTER(tbData,LASTNONBLANK(tbData[Universe],1)))

Last Universe Value:=CALCULATE([Total Universe],FILTER(tbData,tbData[Date]=[Last Universe Date]))

Last Universe Date returns the correct date, but Last Universe Value returns the Grand Total value.

Any help/ guidance is appreciated.

2
daxpowerpivot
Your question is pretty vague. Do you want the latest (most recent) for all values in your table, for a given year, for a given date? - Randy Minder
There are several recent questions on very similar challenges. Can you share with us the code you've tried? Here's a similar question with multiple solutions shown - greggyb
@RandyMinder I want the most recent Universe value when Universe is blank, ie. no implicit value. In the above example, for Channel A I want Universe to return most recent (ie. July value 898), and for Channel B I want Universe to return most recent (ie. July value 1383) - Dean
@Dean - I'm sorry. I still don't follow you. You don't have any blank Universe values in your example. Or, are the yellow highlighted cells supposed to represent values that are blank, even though they actually contain a value? - Randy Minder
@RandyMinder, there are 2 tables in my post, can you not view both? First table is current table, the second (with yellow) is the desired output. The desired output is what I would like the Universe to retrun based on the first table in my post... - Dean

2 Answers

0
votes

I think this is pretty straightforward, unless I'm missing something. In your measure, test if the current Universe value is blank. If it is, find the last Universe value that occurs before this date. Pseudo code might look like this:

Universe Value = 
   VAR CurrentDate = MIN('Date')

   RETURN
      IF (ISBLANK(Universe), <Find most recent Universe value less than 'CurrentDate'>, Universe)

Finding the last date, if it's based purely on date, could be done with something like this. There are of course other ways you might want to do this. This was my first thought.

LastDate = TOPN(
       1,
       FILTER(
          ALL('Date'),
          'Date'[Date] < CurrentDate
          )
       ),
       'Date'[Date],
       DESC
    )

I would probably store this in a variable and then retrieve the value associated with this date if the current Universe value is blank.

0
votes
Total Universe:= SUM ( 'tbData'[Universe] )

Last Universe Value:=
VAR MostRecentDate = MAX ( 'tbData'[Date] )
VAR MostRecentNonBlank =
  CALCULATE (
    MAX ( 'tbData'[Date] ),
    ALL ( 'tbData'[Date], 'tbData'[Year], 'tbData'[Month] ), // you should really pull this out to
                                                             // a date dimension
    'tbData'[Date] <= MostRecentDate,
    NOT ( ISBLANK ( 'tbData'[Universe] ) )
  )
RETURN
  CALCULATE (
    [Total Universe],
    ALL ( 'tbData'[Date], 'tbData'[Year], 'tbData'[Month] ),
    'tbData'[Date] = MostRecentNonBlank
  )

This preserves filter context on 'tbData'[Channel] and 'tbData'[CPT]. If you don't want that, you can throw those into the ALLs as well. Your original FILTER referencing a measure was performing a context transition from the row context of FILTER's iteration into filter context for the measure evaluation. You can read more about this topic here.