Multiple Files in folder and updated weekly for Monthly Report

[Xandman]

Hi

I'm fairly new to Power BI. I have a folder that is updated weekly and I need to get the lastest file for each month.

I found a possible solution on youtube: https://www.youtube.com/watch?v=HBFc1eBSfb8&t=479s   

But, I can't get it to work. 

still shows the multiple files

new measure

Effective date is a column inside the spreadsheet that indicates the date a file was generated. 

[Xandman]

Hi for now, I've resorted to deleting old files in the folders so only 1 per month is shown. Hope this could be corrected in the future. Thanks!

[Xandman]

I'm confused on how to apply the solutions provided. I'm attaching some screenshots and the file. Hope can guide me. I removed the columns and measures I got from the forums solutions and left the data as is. 

Here's the PBIX in dropbox: https://www.dropbox.com/s/z6fkz92m2815cfo/LearningPowerBi.pbix?dl=0 

folder structure

[v-lionel-msft]

Hi @Xandman ,

Do the fields in the table visual above come from the same table?
Although you use the ALL() function in the formula to remove the filters on the table "Package Counts and Amounts and Revenue", the fields of other tables will still change the filter context of the formula.

Please try this formula.

MaxDatePerFile = 
CALCULATE(
    MAX([Effective date]),
    FILTER(
        ALL('Package Counts and Amounts and Revenue'),
        [MonYr] = MAX([MonYr])
    ),
    ALL('Other tables')
)

Please provide sample .pbix file or sample data if this formula is useless.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Xandman]

Hi, I managed to try this formula as a new column but it returns the lastest file from the latest month. 

Currently my sample has 3 months and I'm trying to capture the lastest file for each month. 

[AllisonKennedy]

@Xandman  It sounds like you're trying to filter a folder of files before doing visualizations in Power BI? You should do this in Power Query Editor using M code, rather than in DAX. You don't need to know M for basic things, but need to be able to navigate the Transform Sample File that Power Query creates. Can you share some sample files for past few months and what you want as end result?

[Xandman]

Hi I shared some samples of my file. I managed to figure out how to get some more info from my folders. Now the table includes the name of the folders (which is from year folder "2021" and month folder) in the first 2 columns . 

now has the "Year" folder and "Month" included in the table

Though, I still can't figure out how to get the latest files of each folder. 

Ultimately, I know this can be solved by linking each folder individually and just build relationships around it but, if possible I just would like to upload the files into the folders and Power Bi groups and sorts it by itself.