Easy Measure

[ElliotP]

Afternoon,

I've got some data which comes in quarterly and I'd like to show the latest value and then a measure to show the value before that assuming it isn't null.

I have my date columns, my two data columns (just numbers). I created an index.

Any idea on how to create a measure that would show the latest value if it isn't blank and then a measure to do the same but -1 from the index.

[Greg_Deckler · Accepted solution]

I imagine that there are easier ways to do this, but one way would be to create a calculated table:

TableData1 = CALCULATETABLE(Data,Data[Data1]<>BLANK())

Then you measure:

Measure Data 1 = CALCULATE(SUM(TableData1[Data1]),FILTER(TableData1,TableData1[Index]=MAX(TableData1[Index])))

The first part ensures no blanks, the second part filters to the max of the index.

My sample data was this:

Index         Date          Data1   Data2

1	3/1/2017	10	20
2	3/2/2017	20	30
3	3/3/2017	30	40
4	3/4/2017	40
5	3/5/2017		60

[ElliotP]

First part works a charm thank you.

How would I create a measure which was one less from the index (to show the prior value);

I've tried either version of this to no help;

Measure Data Income-1 = CALCULATE(SUM(TableData1[Income%Change]),FILTER(TableData1,TableData1[Index]=MAX(TableData1[Index]) - 1))

[Greg_Deckler]

Well, you could always do something like:

TableData2 = FILTER(TableData1,Tabledata1[Index]<MAX(TableData1[Index]))

[ElliotP]

Would do you think the measure would be for the value preceding the latest value? = max -1 type situation?

[ElliotP]

turns out that's how to do it lads. thank you so much for your help.