Forum Discussion
Easy Measure
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.
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
5 Replies
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 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))
Well, you could always do something like:
TableData2 = FILTER(TableData1,Tabledata1[Index]<MAX(TableData1[Index]))
