Forum Discussion
Solved
aggregation
What I want to do ----------------------- I want to group/aggregate monthly data by client into annual data by client. I can do it, if the year is calendar year. The problem comes when I want to ag...
If it works for you that is great! It is like so many things in Excel or Power BI - there are a number of ways to come at solving the problem. As long as you find one that works, go forward with it.
Hi edhans,
I had to experiment with a few things based on your idea and finally found a way to make it work.
This is what I did. Again, if you think of a more efficient way, please let me know.
- Copied and split the date column into two columns. One for month and one for year. Month is text here and year is numeric.
- For months from July to December, I replaced them with "1." For January to June, I replaced with "0." I did this for one month at a time.
- I changed the Type for month column to number.
- I added another column that added the value in the newly created month column to the value in year column. I labeled this column Fiscal Year.
- The Fiscal Year column has the original year for January thru June months. For July thru December, the year number is the original year plus 1. For example, the year for July 18 will be 19. That’s what the correct Fiscal Year for July 18 is.
- This Fiscal Year column now has the correct fiscal year for all rows. So, I simply group the data based on this column.
Thanks again for your help!
Satish
If it works for you that is great! It is like so many things in Excel or Power BI - there are a number of ways to come at solving the problem. As long as you find one that works, go forward with it.
Got it. Thanks again.
Satish