Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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 |
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]))
Would do you think the measure would be for the value preceding the latest value? = max -1 type situation?
turns out that's how to do it lads. thank you so much for your help.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |