Forum Discussion
Compare values between rows of custom data
Hi I'm new to Power BI.
Why does
#"Added Index"{[Index]-12}[Total]work only if [Total] is not an added/custom column?
How do I get this to work on custom culumns? I'm trying to compare historical data to current. The data I'm comparing is partially conditional and partially calculated into it's own custom column in the Query Editor. I realize the first 12 rows will show an error due to a negative index, but everything is getting an error. This does not happen if the data is original source data.
5 Replies
Hi,
Please explain the business question. Also, what result are you expecting?
In the end I want a visuaization of the current amounts (posts on specific webpages) and the total from the same month in the previous year. That way we can easily compare to how well we did in the previous year. I can't figure out how to get the "Quick Measure" for YoY to work.
My data is the first 4 columns, I calculate the total, then I would like to just shift the total down 12 lines. I don't know if this is the appropreate method. It seems like it should be simple.
Date Page1 Page2 Page3 Total YoY Total 10/1/2015 127 24 121 272 11/1/2015 15 9 126 150 12/1/2015 28 20 125 173 1/1/2016 2 13 101 116 2/1/2016 5 1 109 115 3/1/2016 5 14 144 163 4/1/2016 17 10 106 133 5/1/2016 5 11 113 129 6/1/2016 12 7 121 140 7/1/2016 4 5 124 133 8/1/2016 3 8 127 138 9/1/2016 3 13 105 121 10/1/2016 21 30 89 140 272 11/1/2016 0 12 79 91 150 12/1/2016 1 12 86 99 173 1/1/2017 2 24 86 112 116 2/1/2017 2 13 94 109 115 3/1/2017 32 9 120 161 163 4/1/2017 36 15 124 175 133 5/1/2017 67 21 73 161 129 6/1/2017 58 34 167 259 140 7/1/2017 41 12 85 138 133 8/1/2017 13 41 151 205 138 9/1/2017 23 18 91 132 121 10/1/2017 11 12 117 140 140 11/1/2017 5 13 104 122 91 12/1/2017 8 8 93 109 99 1/1/2018 13 10 118 141 112 2/1/2018 15 4 114 133 109 3/1/2018 18 15 121 154 161
You can create a measure that is equal to the sum of your three columns.
Then you can create a measure that is equal to the sum of your three columns from the same period last year using
Calculate(yourmeasure, sameperiodlastyear(date))
https://msdn.microsoft.com/en-us/library/ee634972.aspx <----Sameperiodlastyear documentaiton
