Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
satishgupta
Frequent Visitor

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 aggregate based on Fiscal year that goes from July to June.

 

How I did calendar year aggregation?

------------------------------------------------

Month is one of the columns in my table. I used “Group By” function in Query and selected aggregation based on that monthly column.

 

The issue with Fiscal Year aggregation

-------------------------------------------------

If fiscal year information was in the same table as revenue, I could do exactly what I did for calendar year aggregation. The problem is that fiscal year information is in a separate table that assigns each month to a fiscal year.

 

How can I accomplish what I am trying to do?

 

Thanks.

Satish

1 ACCEPTED SOLUTION

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

You need to assign each month the month number it is for their fiscal year. So for calendar year, January is 1, December is 12. For the fiscal year customer, in your example, July is 1, June is 12.

 

Then aggregate by that month number, but keep the dates in your aggregation if you want to split it back out and identify February as February, regardless if it is month 2 for one customer or month 8 for another.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you.

How would I handle multiple years? I also keep adding new months. Is there a way to make it dynamic so that it automatically handles additional months?

I just thought of an indirect way of doing it using your suggestion, but if there is an easier way, I would like to use that.

Thanks again.
Satish

You would retain the original date in your data, so if you have Feb 1, 2017, Feb 1, 2018, and Feb 1, 2019, those would all be month 2 (or 😎  depending on the cal/fiscal year, but since the year was retained, you would still have the year. 

I would map this out in Excel in a spreadsheet to get your logic right on how you want it to look and report, then build your Power Query based on that logic by just adding more columns of date information



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Got it.  Thanks again.

 

Satish

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.