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

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 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Calculation Between Two Columns - Rolling 24 Months - New Month Gets Added, Oldest Month Drops O

Hi @bvbull200 ,

You can transform your data structure to get what you want. I just choose two months data to test. This is my PBIX file.

1. Delete ‘Changed Type’ step after ‘Promoted Headers.’

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

2. Select column ‘Location’, unpivot other columns.

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

3. Split column ‘Attribute’.

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

4. Change type.

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

5. Pivot column ‘Attribute.1’

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

6. Rename column.

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

Then you can create columns and measures.

Columns:

Year = YEAR('Rolling 2 Months'[Date])
Month = MONTH('Rolling 2 Months'[Date])

Measures:

Measure1 =
DIVIDE (
    SUM ( 'Rolling 2 Months'[Revenue] ),
    SUM ( 'Rolling 2 Months'[Business Days] )
)
Measure2 =
VAR previous_year =
    MAX ( 'Rolling 2 Months'[Year] ) - 1
VAR month =
    MAX ( 'Rolling 2 Months'[Month] )
RETURN
    CALCULATE (
        MAXX ( 'Rolling 2 Months', [Measure1] ),
        FILTER (
            ALLEXCEPT ( 'Rolling 2 Months', 'Rolling 2 Months'[Location] ),
            previous_year = 'Rolling 2 Months'[Year]
                && month = 'Rolling 2 Months'[Month]
        )
    )
DAG = DIVIDE( [Measure1]-[Measure2] ,[Measure1])

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

Then I just change the columns name and refresh in Power BI Desktop.

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

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

View solution in original post

4 REPLIES 4
Community Support
Community Support

Re: Calculation Between Two Columns - Rolling 24 Months - New Month Gets Added, Oldest Month Drops O

Hi @bvbull200 ,

You can transform your data structure to get what you want. I just choose two months data to test. This is my PBIX file.

1. Delete ‘Changed Type’ step after ‘Promoted Headers.’

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

2. Select column ‘Location’, unpivot other columns.

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

3. Split column ‘Attribute’.

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

4. Change type.

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

5. Pivot column ‘Attribute.1’

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

6. Rename column.

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

Then you can create columns and measures.

Columns:

Year = YEAR('Rolling 2 Months'[Date])
Month = MONTH('Rolling 2 Months'[Date])

Measures:

Measure1 =
DIVIDE (
    SUM ( 'Rolling 2 Months'[Revenue] ),
    SUM ( 'Rolling 2 Months'[Business Days] )
)
Measure2 =
VAR previous_year =
    MAX ( 'Rolling 2 Months'[Year] ) - 1
VAR month =
    MAX ( 'Rolling 2 Months'[Month] )
RETURN
    CALCULATE (
        MAXX ( 'Rolling 2 Months', [Measure1] ),
        FILTER (
            ALLEXCEPT ( 'Rolling 2 Months', 'Rolling 2 Months'[Location] ),
            previous_year = 'Rolling 2 Months'[Year]
                && month = 'Rolling 2 Months'[Month]
        )
    )
DAG = DIVIDE( [Measure1]-[Measure2] ,[Measure1])

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

Then I just change the columns name and refresh in Power BI Desktop.

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

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

View solution in original post

bvbull200 Helper I
Helper I

Re: Calculation Between Two Columns - Rolling 24 Months - New Month Gets Added, Oldest Month Drops O

This looks great!  I am going to run through it here shortly and see if I can make this workable.

 

I appreciate the detailed response.  I will report back once implemented. 

bvbull200 Helper I
Helper I

Re: Calculation Between Two Columns - Rolling 24 Months - New Month Gets Added, Oldest Month Drops O

Many thanks.  This has done exactly what I needed it to do.  I appreciate the insightful reply.  I will mark as an accepted solution!

Highlighted
bvbull200 Helper I
Helper I

Re: Calculation Between Two Columns - Rolling 24 Months - New Month Gets Added, Oldest Month Drops O

I'm having a problem with the DAG function.

 

When I run the most recent month (1 month only), it works fine.  As soon as I expand in to more than 1 month, the data gets way off.

 

In the table you provided, the sum total of measure 1 and measure 2 doesn't make sense to me and I think whatever is happening there is causing the problem.

 

When I look at my Measure1 right now, every value is 50,000 or higher (like in the example above) and there are 42 rows, but the table sum at the bottom is just 58,905.88.  The revenue column appears to be adding up correctly, just not the measures.

 

Any idea what is going on there?

 

The data for a given sales office for measure 1 and measure 2 appears to be correct.  When I sum up Measure1 manually, then sum up Measure2 manually and do the math of - (Measure1 - Measure2) / Measure2 - manually, I get the right answer.  The measure being done through the PowerBI measures are yielding a different result, however.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors