Forum Discussion
Create new Reference table for Financial Year based on date range
SharePoint site contains folders by Years eg
Year2022
Year2023
Year2024
Year2025
Each folder contains many (100s) of .csv files. reports with excact same tab and columns
I would like 2 tables
1. table for Calendar year. eg 1/01/2022 - 31/01/2022. Done
2. table for Australian Financial year date range 1/07 - 30/06. for example
1/07/2022 - 30/06/2023
1/07/2023 - 30/06/2024
1/07/2024 - 30/06/2025 etc
I created a new financial table, by creating a new Reference table which combines both tables into a single table containing data from 01/01/2022 – 31/12/2023
Year2022 (contains dated from 01/01/2022 - 31/12/2023)
Year2023 (contains dates from 01/01/2023 - 31/12/2024)
I am still obtaining data source .csv files and adding to past folders. Due to I have inherited this task from another team, and they are handing over files. Therefore I assume the new tables to be created as 'Referenced' tables.
I followed this video to load the files
The best way to connect to a SharePoint Folder to speed up your Excel and Power BI data refresh https://www.youtube.com/watch?v=-XE7HEZbQiY
What is the best way to create the financial year tables
TIA
dd88 , Do want to create overlapping years?
You can FY Column in same table as Calendar year table
1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
Power BI Date Table: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=18180s
Join between would be tricky and in DAX you have bring column by column. You can add columns based on the logic given in blog or attcahed file.
3 Replies
dd88 , Do want to create overlapping years?
You can FY Column in same table as Calendar year table
1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
Power BI Date Table: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=18180s
Join between would be tricky and in DAX you have bring column by column. You can add columns based on the logic given in blog or attcahed file.awesome I will have a look. many thanks amitchandak
Hi dd88 ,
amitchandak's reply was great.
Pls has your problem been solved? If so, accept his reply as a solution. This will make it easier for the future people to find the answer quickly.
If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.
Best Regards,
Stephen Tao
