Log In
Register
Home
February 19, 2023
Offline
Dear All,
I need to calculate average dynamically based on the months selected. The Run rate calculation should exclude the latest month.
The months are in columns and called M1 Actual, M2 Actual and so on.
Financial Year starts from April to March, April being the first month and March is the 12th.
Please see the attachment..
Moderators
January 31, 2022
Offline
One way would be to use AVERAGE and OFFSET, where the offset is based on the first blank cell found in the range under M1 to M12. See attached.
February 19, 2023
Offline
Hi Riny,
Thanks for your reply.
Apology, I forgot to mention, this calculation I would like to do in Power Pivot using DAX.
So, when I create power Pivot (Pivot Table), and select the months and bring in the Run Rate measure, it should give me results as per your excel file shared or my picture.
Thanks.
Moderators
January 31, 2022
Offline
Fair enough. Hadn't noticed the comment re Power Pivot in the screen shot. Please upload an example file so that we don't have to construct something ourselves.
February 19, 2023
Offline
Hi Riny,
Please see the attached Sample file.
Please note couple of things:
In my Original file, the data is pulled through Power query from a folder and then loaded into a pivot table via a connection and added to Data Model as well.
The calculation should be dynamic based on the no of months selected in the pivot table (Power Pivot).
In my sample file, you will find the same but I have kept the data in the same file.
The result I want is highlighted in Green.
Thanks for your help!
Moderators
January 31, 2022
Offline
I believe your data is not in the correct format for the result you want to achieve in Power Pivot (PP). It's perfect, though, to work in Excel with formulas as I demonstrated earlier.
The data is already in a pivoted report like format. Use Power Query (PQ) to unpivot it first. Then, rater than using M1, M2 etc. to indicate the months, use real dates. That way you could use time intelligence functions in Power Pivot/DAX with the help of a calendar table. I haven't gone that far as I don't understand why you would want to do this in PP to begin with.
February 19, 2023
Offline
Well, my data is huge, there are two years of data pulled by Power query (way to many rows). Not sure if Power query can do this kind of calculation and still be very quick to produce the result. I can't think of any other way of doing this. As we are using PP in the final outcome, so that's the only reason I choose do it in PP.
Also, am not sure how can I unpivot and use real dates instead of M1, M2 etc.
I would appreciate your expert advice on how to achieve the desired result?
Moderators
January 31, 2022
Offline
Really sorry, but there's not much I can offer if you don't provide some real (though anonymized) data and the model you have already built (i.e. from raw data to PQ to PP/DM)) . It seem that the Sample data you provided earlier is very limited and probably not providing the complete picture. When you say that the data is 'huge' and covers two years, surely there must be real dates in there, that converted to M1, M2 etc.
But perhaps I'm totally missing your point. Then perhaps someone else can step in.
February 19, 2023
Offline
Thanks Riny for your reply.
The sample data is the depiction of real data. There are no dates, it just when we recieve the data for new month it goes to the relevant month column.
If we have already completed 3 months, you will see numbers in M1 Actual, M2 Actual, M3 Actual but M4 columnnwill be blank. Once M4 data is there, on refreshing M4 Actual numbers will be in that column.
The reason the real dates are not there beacause this source data shows you sum of all the daily transcations by cost cente code and Account code for that particular month.
I hope that clears the fog.
Many Thanks for spending your time on trying to resolve my query.
Moderators
January 31, 2022
Offline
"I hope that clears the fog."
Not really. Sorry. You said you have two years of data. So, for the previous year, you would record grouped data in the same columns M1 to M12 but with a FiscalYear of "2023/24". Perhaps I'm too harsh, but I believe that is not the way to go.
When you keep all daily transaction with the actual dates in the data base, you can set-up a Date table in PP, customized for your FiscalYear and label each date in the Date table with the appropriate label for the month they fall in (i.e. M1 to M12).
Although I haven't thought about it in any further detail, it should then be possible to calculate the average numbers you seek with a series of DAX measures.
February 19, 2023
Offline
Hi Riny,
Yes you are right, for the previous year, you would record grouped data in the same columns M1 to M12 but with a FiscalYear of "2023/24".
Unfortunatley, the source file has been developed by another team in this way, I can't chnage anything there.
I am currently working on creating a Calendar table and unpivoting the data (following your Instruction) and convert the months into actual dates - first of each month and then hopefully will be able to use Time Intelligence functions in PP.
I'll share the workings here and will ask for guidance, if required.
Thanks.
1 Guest(s)
