1. N

    Power Query - Calendar Table

    Good morning - I have a fiscal calendar that my company creates which I pull into Power BI. Columns include date, fiscal year, fiscal week, day of week, work day, holiday, holiday name, week of month, and fiscal quarter. What I want to do is creat a few custom column in power query that will...
  2. J

    Fiscal Period by Date

    Hey everyone, I'm drawing a blank here. I have a workbook for a company that runs on a 13 period fiscal year rather than monthly periods and I need to look up the period based on the date. What formula can I use to look up a date and return the period? I have the following: <tbody> Start...
  3. N

    Macro to Combine Data from Different Columns

    I have a large sales data report containing severalgroups of customers. I would like to automatically generate individual usagereports (separate files so not by tab) from this large file for each groupevery month and save them separately on a shared drive. I would like to also have each...
  4. T

    Extracting Data from a Range that Excludes Blanks

    Hello, I have a spreadsheet with a range of dates based on fiscal years from 10/01/2019 through 09/30/2022. I'm trying to use a formula that looks at this list of dates in a column and then extracts only the dates that occur within fiscal year 2020 which is from 10/01/2019 through 09/03/2020...
  5. N

    How to Create Multiple Reports from One Sheet

    I have a large sales data report containing several groups of customers. I would like to automatically generate individual usagereports (separate files so not by tab) for each group every month and save themon a shared drive. I would like to alsohave each report automatically be customized...
  6. C

    IF Statement using Networkdays.intl formula

    I am trying to build a formula that will calculate after determining if a start and end date are equal to or between a calendar (fiscal) start and end date. The formula I have is...
  7. R

    Changing PeriodID to Fiscal periods

    In Power Query I have a PeriodID column that starts at 1 and increases by 1 every 28 days, so each fiscal period has a unique number. I am trying to find a way to convert these numbers to periods, so it will repeat number 1 through 13 every year. Here is my current code: Table.AddColumn(#"Added...
  8. A

    Calculate Fiscal Year Date

    I have a cell a1 with date say "07/31/2016", I need to populate cell b2 with a date that can calculate fiscal year, when the fiscal year is not same as calendar year. The fiscal year runs from 08/1/20xx to 07/31/20xx+1, For example: for date 07/15/2016 the fiscal year should end on 07/31/2016...
  9. DPChristman

    Creating a Fiscal Week Lookup Table

    I am trying to create a fiscal week lookup table for a spreadsheet I am working on. My boss needs TY/LY weekly comparison. I have tried to use the =weeknum(b1) command. However, our first fiscal week is 2/3/19-2/9/19, so the value returned using that formula is 6. I am able to get it to read 1...
  10. mikecox39

    Formula to pull year from a date code and add to a string

    I have a budget sheet that I am trying to automate. I want to create a new sheet at the end of each fiscal year. I want the current year for each charter to be picked up from the date I enter.
  11. T

    VBA Cut and Paste Data From One Sheet to appropriate Sheet based on FY Date

    Hello, I am hoping someone can help me with my dilemma, I don't even know where to begin coding this one. I have a Workbook with 6 Sheets: TRACKER, FY19 SUMMARY, FY20 SUMMARY, FY21 SUMMARY, FY22 SUMMARY, FY23SUMMARY, and FY24 SUMMARY. I need to move a portion of a row of data from the...
  12. N

    SUMIF COUNTIF AVERAGE problem formula...

    Hi all, hoping that somebody can help with this formula please... I have a list of contractors, all with multiple lines for queries (for different pieces of work), with the number of days they take to respond to each query and the fiscal year that the query falls into. So I have the following...
  13. L

    Power Query to Get Fiscal Year & Fiscal Month

    My fiscal year is 10/1 - 9/30 and I would like to modify the following M code so that it reflect the fiscal month and year correctly. Can someone help? So October 2018 should be fiscal month 1, January 2019 should be fiscal month 4, July 2019 should be fiscal month 10, etc. let...
  14. J

    Monthly split from date range

    Hi, I'm trying to find the best formula for my spreadsheet that shows me the number of months within a fiscal year, with a start and end date range . Could someone help?
  15. B

    Formula if one cell is within a range of numbers to return Year from next cell

    I have four columns. A is calendar month, B is calendar year, C is Fiscal month, D is going to be Fiscal year. Our fiscal year is April to May, April = Fiscal month #1 , May = 2, etc. I need a formula for the fiscal year that will look at the fiscal month cell. Say C2 is either 1,2,3 fiscal...
  16. C

    Determine number of months between two dates based on Fiscal year.

    Our fiscal year is from April 1 to March 31: example April 1, 2018, thru March 31, 2019, in FY 2019. We have savings initiatives that may start prior to or during those months. Savings are recorded as the average of the savings divided by 12 months: 120k savings is 10k per month. So if...
  17. L

    Semi-Dynamic Fiscal Year & Quarter Formula

    I have a workbook with the following dynamic formula that determines the fiscal year and quarter based on todays date. When I run the formula today it will result in "FY 2019 Q2" but when I open the same workbook in April 2019 it will change to "FY 2019 Q3" but I do not want that to happen...
  18. B

    Month counting formula between 2 dates, including months per fiscal year

    Hi, We take on contracts typically ranging from 3 months to 36 months. we always use the 1st of the month for the start and end dates. for example: Contract 1: 1/4/18-1/12/18 = duration 8 months Contract 2: 1/2/18 - 1/2/18 = duration 12 months Contract 3: 1/1/18 - 1/1/20 = duration 24 months...
  19. G


    Hi Would appreciate any help with this GETPIVOTDATA, I need to make it dynamic, and link &[2.018E3] to a year in a cell and&[1.] to week in a cell. I've tried a few things but nothing is working. =GETPIVOTDATA("[Measures].[MP Count]",'[3Data.xlsx]ATT ENT'!$A$4,"[Subscriptions].[Attend...
  20. K

    Fiscal Year Help Wanted

    Hi all, I am in a serious quandary! I have searched the internet and I cannot locate a clear solution to adding a column (or multiple columns) in Power Query that will calculate the Year, Quarter, Month, Week according to our clients fiscal year. I have created a dashboard according to the...

Some videos you may like

This Week's Hot Topics