Create new Reference table for Financial Year based on date range

[dd88]

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

[amitchandak]

@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-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
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.

[dd88]

awesome I will have a look. many thanks @amitchandak 

[v-stephen-msft]

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