cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvbull200
Helper II
Helper II

Calculation Between Two Columns - Rolling 24 Months - New Month Gets Added, Oldest Month Drops Off

I've hit a roadblock on a report I'm working on.

 

I have data like the attached.  It shows a list of locations, then has the revenue for each of the past 24 months and the number of business days in each of the last 24 months.  With this, I can calculate daily average growth (DAG). 

 

With static data, it is easy to do the following calculation:

 

(((Revenue AUG 2019)/(Business Days in AUG 2019))-((Revenue AUG 2018)/(Business Days in AUG 2018)))/(Revenue AUG 2019)/(Business Days in AUG 2019)

 

The problem is, the oldest month on the report will drop off and be replaced by the most recent month.

 

In this case, it means that, next month, SEP 2017 will fall off and be replaced by SEP 2019.  As such, I can't build a calculation off of existing header names - they will be changing month to month.

 

Is there a way to do a calculation based off of the column number rather than the column name?  For instance, take the above calculation and replace header names with a column number?

 

Is there another solution I should be searching?

 

Ideally, when I am done, I will have Revenue, Business Days, and DAG for every sales location for the last 12 months.

 

 

This is a sample of the data.  It goes out to 48 columns in the query.

 

LocationRevenue AUG 2019Business Days AUG 2019Revenue JUL 2019Business Days JUL 2019Revenue JUN 2019Business Days JUN 2019Revenue MAY 2019Business Days MAY 2019Revenue
APR 2019
Business Days APR 2019
Alabama1382689.5  211269324.34  211369134.09  211078965.78  211000799.88  21
Alaska1458752.43  211409564.96  211272245.54  211347723.84  211127298.6  21
California1620720.21  211334704.87  211040614.8  211201193.03  211182246.59  21
Deleware1978296.83  212291016.27  212109485.17  212190225.58  212106644.09  21
Florida968349.04  211119913.61  21979007.91  211078147.98  211199920.92  21
Georgia1741838.23  211869439.93  211605837.01  211854699.02  211903840.14  21
1 REPLY 1
v-diye-msft
Community Support
Community Support

Hi @bvbull200 

 

Sorry i'm a little confused with your request, could you please share your dummy pbix/complete worksheet to indicate the expected data? list all the column data with expected result and simple logic would be helpful for us to generate the valuable advice.

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.