DAX measure Pick up the latest file.

[Saxon10]

Hi,

I have a two tables are one is data and another one is report. Data file contain “item” and “sales code” and report file contain “item”.

In order to pull the sales code against the item in report file I am using following DAX measure lookvalue. (from data file to report file).

 The folder name is "Sale Data" under the "Sales Data" folder I am stored the daily sales details in Excel.

The sales file name is "TRP_KPY" always same but date and time keep changing on a daily basis “TRP_KPY_07-11-2020_08:00".

 The sales file data structure are same (headers and headers orders always same manner "Item, sales code") 

 I am trying to get the report against the latest file but currently I am changing the data source manually.

Example:

Folder Name is "Sale Data".

File name:

“TRP_KPY_07-11-2020_08:00"

“TRP_KPY_06-11-2020_08:30"

“TRP_KPY_05-11-2020_08:00"

“TRP_KPY_04-11-2020_07:00"

“TRP_KPY_03-11-2020_01:00"

How can I automated the report? (DAX measure pull the sales code against the item corresponding to the latest file)

Could you please advise.  

[mahoneypat · Accepted solution]

Here is how to do this.  In your existing query (assuming you use the Folder connector to get a list of files), you probably generated a step like this when you selected the file to use (check the View Formula Bar if you don't see it).

= #"Filtered Rows"{[#"Folder Path"="C:\Folder\Subfolder",Name="examplefile.csv"]}[Content]

To get the latest file instead, at the previous step sort the table descending by modified date, and then revise the above to this

= #"Filtered Rows"{0}[Content]

This references the first row and since you sorted descending, it will give you the contents of the latest file.

Regards,

Pat

[Saxon10]

Hi. Thanks for your quick response. I am new to Power Bi. If possible could you please provide the snap shot step by step in order to understand your solution so I can try my end and I will update the feedback to you. 

[mahoneypat]

This video walks through it (almost the same approach).

https://www.youtube.com/watch?v=m9XCmS0UzHc

Regards,

Pat

[Saxon10]

You are star. Thank you so much for your guideline. It's very uselfull.