Log In
Register
Home
Power Query
Power Pivot
Power BI
January 29, 2019
Offline
HI Mynda,
We run data reports several times within the same month. The values change monthly based upon who did not pay their bill etc.Ex: If I ran June data in July it showed 100 people. Say 10 people don't pay their bill, when I run the June data again, it will show 80.
If: Report Data (June) = Report data (June row above) then Value - value (row above)
I want to add a column for variance between the two values, in this example it would be 20.
I can calculate this within Excel. I am looking on how to calculate within Power Query? I cannot use an index for the calculation as I need the calculation to recognize the difference in the month.
I"m attaching a screenshot of the actual report. Will you please review and advise if this is possible? I've done a lot of searching. 
See Answer
July 16, 2010
Offline
Hi Alison,
You can use this technique to reference the next row in Power Query. You'll want to bring the ReportData date column in again so you can compare the columns to make sure they're the same period before performing the variance calculation.
Mynda
Power Query
Power Pivot
Power BI
January 29, 2019
Offline
I'm sorry, I'm unable to work through the above.
I ended up adding my variables within the table. So I then import to Power BI and am having another issue.
When trying to have visuals with "#Members" by date, Power BI wanted to sum. I checked "Don't summarize". Now when I add the "Members" to the values pane it is counted. I'm pulling my hair out.
I have successfully done this before, I don't understand what I am doing wrong. I will upload the files for you. The main table from excel I use is "PremiumMember"
July 16, 2010
Offline
Hi Allison,
Sorry, I should have said that you needed to follow Option 2: Duplicate data and merge. See file attached - PremMember sheet from column M onward. Note: I had to copy your data because I didn't have access to the source file referenced in the query.
In regards to your Power BI issue; if you want to see Members by Date then you need to put the Date field in the Axis, not in the values area. The Values area only counts/sums/max/min etc.
Mynda
Answers Post
1 Guest(s)
