Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |