Measure to show last previous value sorted on change key

[Anonymous]

I have a problem figuring out a way to have a measure to display the value of a column for a given date. I have the following table setup:

Calendar Table

ID - Integer, Primary Key

Date - Date, Calendar date

Changelog Table

ID - Integer, Primary Key

ChangeID - Integer, Change Key

Datekey - Integer, Foreign Key to Calendar Table with relationship

ChangeValue, Integer Value after source has changed

What I want is a measure that given any date it should return the last value for ChangeValue sorted by the ChangeID column and filtered by calendar table (sometimes there are more than 1 change per date so just using Datekey is not sufficient). And prefereably do so as effieciently as possible. What the end goal is to be able to draw a line for each calendar date based on changelog data (ie. historical values).

So far I've had some success using this formula:

CALCULATE(LASTNONBLANK(Changelog[ChangeValue];Changelog[ChangeValue]);FILTER(ALL(CALENDAR);CALENDAR[ID]<=MAX(CALENDAR[ID])))

But it's pretty slow (filter on Calendar table for each date takes time I suspect), and it does not utilize ChangeID to identify the latest change (instead I get the largest ChangeValue).

Is there any way to enforce sorting according to ChangeID? Or a better way to construct the data?

[v-juanli-msft]

Hi @Anonymous

add "date" column from Calendar Table to a slicer, then create measures

Measure 2 =
CALCULATE (
    MAX ( [ID ] ),
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[ChangeID] ),
        Sheet2[Datekey] <= SELECTEDVALUE ( Sheet1[Date] )
    )
)



Measure 3 =
CALCULATE (
    MAX ( [ChangeValue] ),
    FILTER ( ALLEXCEPT ( Sheet2, Sheet2[ChangeID] ), [ID ] = [Measure 2] )
)

Then when you select a date in the slicer, the [Measure 3] would show the lastest changevalue for each change id by the selected date.

Please note that you need manage the relationship between two tables as below

Best Regards

Maggie

[Anonymous]

Hello

Unfortunately I couldn't get your method to work. Probably because I was explaining my case poorly. What I'm trying to do is to do a comparisson of inventory stocks to reorder points and have this track historically in a line chart similar to this picture:

I did find a way to get it to do what I wanted though using the dax function LOOKUPVALUE:

Measure = 
CALCULATE(
  LOOKUPVALUE(
    Changelog[VALUE];
    Changelog[ID];
    MAX(Changelog[ID])
  );
  FILTER(
    ALL(CALENDAR);
    CALENDAR[ID]<=MAX(CALENDAR[ID])
  )
)

To get this to work I had to enforce sorting according to date and ChangeID when I loaded the changelog table. Calendar ID is sorted by date already so I use the integer keys rather than actual dates.

The downsides of this method is it only works on one inventory item at a time (as there's no clear identifier for different inventory itemnumbers), which is something that can be improved upon, and that the use of calculate puts a boatload of strain on the system when using day as your detail level. This leads to it being very taxing on both memory and slow to load the visualizations.

So if anyone has a suggestion of a better method I'm still looking for it.

[Anonymous · Accepted solution]

Another day of tinkering and lot's of Google-fu, and I've come up with a much more workable solution.

As mentioned in my previous post, using calculate together with ALL(calendar) creates a pretty hefty workload for each date that is to be calculated. The solution to this was to replace the FILTER function with CROSSFILTER to work around relationship based filtering. The final Measure I came up with was this:

Lookup Measure = 
VAR MaxDate = MAX(CALENDAR[ID]) 
RETURN
CALCULATE(
	LOOKUPVALUE(
		Changelog[VALUE];
		Changelog[ID];
		MAX(Changelog[ID]
	);
	Changelog[Datekey]<=MaxDate;
	CROSSFILTER(Calendar[ID];Changelog[Datekey];None)
)

The second problem was aggregation over multiple items as the[Lookup Measure] needs to be evaluated for each item and then summed to get the correct sum. This was pretty tricky, but I managed to get an aggregation measure working by using the GROUPBY() function:

Aggregation Measure = 
CALCULATE(
	SUMX(
		GROUPBY(
			ADDCOLUMNS(
				GROUPBY(
					Changelog;
					Changelog[ItemKey]
				);
				"LATEST VALUE"; [Lookup Measure]
			);
			Changelog[ItemKey];
			"SUBGROUP SUM"; SUMX(CURRENTGROUP();[LATEST VALUE])
		);
		[SUBGROUP SUM]
	);
	CROSSFILTER(CALENDAR[ID];Changelog[DateKey];None)
)

And for anyone looking for a single measure solution:

Aggregated Historical Value Measure = 
VAR MaxDate = MAX(CALENDAR[ID]) 
RETURN
CALCULATE(
	SUMX(
		GROUPBY(
			ADDCOLUMNS(
				GROUPBY(
					Changelog;
					Changelog[ItemKey]
				);
				"LATEST VALUE"; CALCULATE(
						LOOKUPVALUE(
							Changelog[VALUE];
							Changelog[ID];
							MAX(Changelog[ID]
						);
						Changelog[Datekey]<=MaxDate;
						CROSSFILTER(Calendar[ID];Changelog[Datekey];None)
					)
				);
			Changelog[ItemKey];
			"SUBGROUP SUM"; SUMX(CURRENTGROUP();[LATEST VALUE])
		);
		[SUBGROUP SUM]
	);
	CROSSFILTER(CALENDAR[ID];Changelog[DateKey];None)
)

Beware that with too large a selection (in terms of ITEMS and/or dates) these measures will eat up all available system memory whilst producing temporary tables. So use with caution.