Great video! Here's a copy of your code to gather the most recent date. Is latest month = 'Fact Table'[Refresh Date] = CALCULATE ( MAX ( 'Fact Table'[Refresh Date] ), FILTER ( ALL ( 'Fact Table' ), 'Fact Table'[Company] = EARLIER ( 'Fact Table'[Company] ) ) )
Thanks its great and precise, i come across this tut while i search auto data getting from ftp server folder to powerbi once the source path(FTP Server with Credential) is given. Hence, may you do that.
Great question! To be honest I don't remember why I wrote this in such a roundabout way, since all I wanted was to compare the max date against the row context date, so this formula would work (and look) better: IsLatestMonth = VAR _thisdate = 'Monthly Report'[Date] VAR _latestdate = MAX('Monthly Report'[Date]) RETURN _thisdate = _latestdate EARLIER is supposed to get a value one level up from the nested calculation
Thanks for your video. How do i have a selector to choose the report i want to display ? for eg, I am able to choose any month i want and the visual display according based on the month i choose. thanks again.
Very useful, thanks! But what do I do if I have files of more than one layout (ie some will be additional transaction data and some will be updated static customer info) that i want to update and use?
Hi there, this solution only works if all the files are structured the same, because the automation has to know how to process all of your files the same way
Can we connect power bi report with a folder in cloud like SharePoint and perform the same thing that you have described here with the folder in local?
Hi Sheeba, you absolutely can! I actually covered this exact topic in a separate video, where I get data from a SharePoint folder: ua-cam.com/video/vFXdZGUGMOc/v-deo.html
how to set refresh option for current month file where as i dont want others files to get refresh in the folder to increase the efficiency of the power BI.
Hi, thanks for the video, If we defined transform steps for file which we received by email and upload the same in PBI. Will the system will transform as per steps define. Pls advise.
Hi Rehan, if I understand your situation correctly, yes! As long as the file you receive by email is the same format as the file you used in Power BI, Power BI will perform the same steps you define when you hit Refresh
Hi there, I can't seem to get this to work. All the values come up as true regardless of whether the date is the most recent - the date format is in DD MMMM YYYY, not sure if that makes a difference. Also as well as the date column is there a way to choose the other column for the formula - in this case its (Country), does it matter? thanks. Playing around with this other column I use I can make it better in that it mostly gets the true/ false statement right - but there are lots of trues that should be false. I think its to do with the date format I use - I need it too look at the whole date not just the months as the data goes back more than a year.
Hi, thanks for the tutorial. I need to do incremental refresh of daily excel data from a shared folder, if I follow the same method will all the files be loaded in every schedule refresh or just the new ones that are added everyday ? Please help me with this, a bit stuck.
Hi Aarti, this method loads every single file on every single refresh, here's a video where I covered incremental refresh: ua-cam.com/video/fZVtlckIYvY/v-deo.html
Thank you for this tutorial. My goal is to adapt my PBI reports to use the "Folder" method. Do I need to recreate my reports when using the "Folder" get data option?
Hi Bryan, thanks for watching! Not necessarily, as long as your dataset ends up with the same fields as what you had before using the folder method, you should be able to adapt it without having to redo everything
This is great. Question, if I upload daily files to the folder, how would I track the changes between each file? Line items that drop off, and new ones that are on the latest report? Differences in values? Thank you!
So I get daily files and the final balances from last file from the previous month are the static starting balances for the new month. Any changes in the balances on every new daily file should only calculate the difference between that file and the previous file balance, not the actual balance shown.
Hi Roger, this sounds very specific, but definitely doable in Power BI. If you get daily files, I'd create two measures, 1 for current day and 1 for previous day (I have a video covering these) then if you want to calculate the variance from the two balances, you just do current day - previous day I've done it in the past to show how much % day difference for sales data, which I cover in passing in this video (ua-cam.com/video/o2jSUHU722w/v-deo.html) Hope this helps, good luck!
Hi I want to connect a folder with same type of files differentiated with dates. This reports placed every week in SharePoint site. I want to connect these all files and create graphs to so weekly progress. Can you help me?
Hi bro, I don't know what is the 'monthly report'[country] = earlier ('monthly report'[country]) do, can you explain please? as I by reading it I'm not sure if its necessary
Hi Tony, not sure what the context of this measure is, but EARLIER is something to check and compare previous data, maybe previous months data vs current month data?
Hi Ezhar, not a noob question at all! Yes it can work with weekly reports! As long as the structure of your reports are the same (same column names, same order) this should work with a bit of modification
For that you have to publish it in power bi services and then in workspace and there you get ellipsis where you can find a auto refresh so you can chose any one from all the three i.e. 0:00, 8 hrs and 16 hrs but forst we have to check the all credential properly
Brilliant! Is this applicable to updating a worksheet with additional data and refreshing in PowerBI? Kindly advise if you did something extra or it was that simple. Thanks
would this still work if you used the same folder for over a year? like when you get to january 2024 is it still going to take december 2023 as the latest month?
Can we use an excel workbook with multiple sheets in it and perform the same automation? I'm trying but getting error that 'The key didn't match any rows in the table' And thanks for this video ✌️
Hi Sheeba, yes of course! You just need to ensure that all your files have the same exact sheets, you can define which sheet Power BI should combine in the process, Check out this video, I covered this part here: ua-cam.com/video/vFXdZGUGMOc/v-deo.html
Hi There, I am trying to use IslatestMonth = 'Monthly Reports'[Date] =CALCULATE(MAX('Monthly Reports'[Date]), FILTER(ALL('Monthly Reports'),' ')). The issue I have with my second expression is the values are not same across my monthly reports. Is there a way I can use the above measure where I can set the latest date as true when I do not have a common/same secondary expression ?
Ey, do you have a video in which you compare always the last 2 months? I have lots of data monthly, and I just need to find the variations between the last month and the current month... Basically I have the name of all my users (imagine 100), all the products by each user (I have 200 products, so each user has 200 rows), and I have the percentage they have advance in each product. Meaning in January Pedro can have 17% advance in Product 58, and in Febraury he can be also in 17% in the same product (he didn't use it), but in the case he use it, I need to identify the variation... So my question is :P! do you have a tutorial for comparing always the last 2 months?
it's so distracting how you are switching the scenes too many times between the screen and yourself. People are busy getting the information with a short and limited time.
Awesome video! I came here looking for one thing specifically and learned so much more!
Appreciate that, thanks for watching!
This is what I exactly need! Thank you so much Sir. Keep it up❤
This is great! Thanks I hope to apply it in my Primavera P6 reports
Brilliant, didn’t realise you could do this. Thanks
Thank you for this video. The Earlier function bit was confusing, do you have a video on that?
Great video! Here's a copy of your code to gather the most recent date.
Is latest month =
'Fact Table'[Refresh Date]
= CALCULATE (
MAX ( 'Fact Table'[Refresh Date] ),
FILTER (
ALL ( 'Fact Table' ),
'Fact Table'[Company]
= EARLIER ( 'Fact Table'[Company] )
)
)
Thanks for watching! Very helpful snippet here!
This is excellent, thank you so much for sharing your knowledge
You are great bro this will work
Hope it helped!
Thank you very much. Your tutorial is very helpful.
Glad it was helpful!
Nice explanation bro
You're welcome, thanks for watching!
Could you please show how to do that in online SharePoint folder ?
Thanks its great and precise, i come across this tut while i search auto data getting from ftp server folder to powerbi once the source path(FTP Server with Credential) is given. Hence, may you do that.
Fantastic video, thank you so much 😊
You are so welcome! Thanks for watching!
Great video!
Thank you very much! It's just truly helpful and direct to the point! Appreciate it :)
You're very welcome, glad it helped!
What does the filter where you used "Earlier" and country do, time of video: 7:00
Great question! To be honest I don't remember why I wrote this in such a roundabout way, since all I wanted was to compare the max date against the row context date, so this formula would work (and look) better:
IsLatestMonth =
VAR
_thisdate = 'Monthly Report'[Date]
VAR
_latestdate = MAX('Monthly Report'[Date])
RETURN
_thisdate = _latestdate
EARLIER is supposed to get a value one level up from the nested calculation
@@SolutionsAbroad Great, this has more readability now.
Thanks for your video. How do i have a selector to choose the report i want to display ? for eg, I am able to choose any month i want and the visual display according based on the month i choose. thanks again.
Very helpful video
How can we do with two different sets of data I have two different sources and each has its own monthly data and need to be filtered by date?
Very useful, thanks! But what do I do if I have files of more than one layout (ie some will be additional transaction data and some will be updated static customer info) that i want to update and use?
very good. thank you
Could I ask if there is any downsides to using such a feature? Like reaching the max data file size etc? Thank you for the great video!
How can you achieve this if the file format is different for a few files?
Hi there, this solution only works if all the files are structured the same, because the automation has to know how to process all of your files the same way
Can we connect power bi report with a folder in cloud like SharePoint and perform the same thing that you have described here with the folder in local?
Hi Sheeba, you absolutely can! I actually covered this exact topic in a separate video, where I get data from a SharePoint folder: ua-cam.com/video/vFXdZGUGMOc/v-deo.html
@@SolutionsAbroad thank you so much!
how to set refresh option for current month file where as i dont want others files to get refresh in the folder to increase the efficiency of the power BI.
Very good tutorial, is this guy still posting helpful video tutorials?
Simple and sweet
Hi, thanks for the video,
If we defined transform steps for file which we received by email and upload the same in PBI. Will the system will transform as per steps define.
Pls advise.
Hi Rehan, if I understand your situation correctly, yes! As long as the file you receive by email is the same format as the file you used in Power BI, Power BI will perform the same steps you define when you hit Refresh
@@SolutionsAbroad thanks
Hi there, I can't seem to get this to work. All the values come up as true regardless of whether the date is the most recent - the date format is in DD MMMM YYYY, not sure if that makes a difference. Also as well as the date column is there a way to choose the other column for the formula - in this case its (Country), does it matter? thanks. Playing around with this other column I use I can make it better in that it mostly gets the true/ false statement right - but there are lots of trues that should be false. I think its to do with the date format I use - I need it too look at the whole date not just the months as the data goes back more than a year.
Awesome!
Great to hear that, thanks for watching!
Hi, thanks for the tutorial. I need to do incremental refresh of daily excel data from a shared folder, if I follow the same method will all the files be loaded in every schedule refresh or just the new ones that are added everyday ? Please help me with this, a bit stuck.
Guys if anyone is aware pls help!!!
Hi Aarti, this method loads every single file on every single refresh, here's a video where I covered incremental refresh: ua-cam.com/video/fZVtlckIYvY/v-deo.html
How do you create a monthly view from live data . The data is dynamic and keeps changing
Thank you for this tutorial. My goal is to adapt my PBI reports to use the "Folder" method. Do I need to recreate my reports when using the "Folder" get data option?
Hi Bryan, thanks for watching!
Not necessarily, as long as your dataset ends up with the same fields as what you had before using the folder method, you should be able to adapt it without having to redo everything
Thank You
I used this same method for the excel file with multiple sheets, but the refreshing doesn't work. Do I have to follow some other method.
This is great. Question, if I upload daily files to the folder, how would I track the changes between each file? Line items that drop off, and new ones that are on the latest report? Differences in values? Thank you!
So I get daily files and the final balances from last file from the previous month are the static starting balances for the new month. Any changes in the balances on every new daily file should only calculate the difference between that file and the previous file balance, not the actual balance shown.
Hi Roger, this sounds very specific, but definitely doable in Power BI.
If you get daily files, I'd create two measures, 1 for current day and 1 for previous day (I have a video covering these)
then if you want to calculate the variance from the two balances, you just do current day - previous day
I've done it in the past to show how much % day difference for sales data, which I cover in passing in this video (ua-cam.com/video/o2jSUHU722w/v-deo.html)
Hope this helps, good luck!
Hi
I want to connect a folder with same type of files differentiated with dates. This reports placed every week in SharePoint site. I want to connect these all files and create graphs to so weekly progress. Can you help me?
Hi bro, I don't know what is the 'monthly report'[country] = earlier ('monthly report'[country]) do, can you explain please? as I by reading it I'm not sure if its necessary
Hi Tony, not sure what the context of this measure is, but EARLIER is something to check and compare previous data, maybe previous months data vs current month data?
Hi Fernan, I have a noob question. Does this method applies for weekly report? Just change the coding a bit?
Hi Ezhar, not a noob question at all! Yes it can work with weekly reports! As long as the structure of your reports are the same (same column names, same order) this should work with a bit of modification
Hi does it work the same with SharePoint folder ?
For that you have to publish it in power bi services and then in workspace and there you get ellipsis where you can find a auto refresh so you can chose any one from all the three i.e. 0:00, 8 hrs and 16 hrs but forst we have to check the all credential properly
Brilliant! Is this applicable to updating a worksheet with additional data and refreshing in PowerBI? Kindly advise if you did something extra or it was that simple. Thanks
Hi, thanks for watching! If the format of the worksheet stays the same, its as simple as hitting the refresh button! I hope that helps
@@SolutionsAbroad Yeah. It worked. Thanks.
Hey may I know, why scheduled Refresh is not working on power bi clouds for folder dataset?
would this still work if you used the same folder for over a year? like when you get to january 2024 is it still going to take december 2023 as the latest month?
Can we use an excel workbook with multiple sheets in it and perform the same automation? I'm trying but getting error that 'The key didn't match any rows in the table'
And thanks for this video ✌️
Hi Sheeba, yes of course! You just need to ensure that all your files have the same exact sheets, you can define which sheet Power BI should combine in the process, Check out this video, I covered this part here: ua-cam.com/video/vFXdZGUGMOc/v-deo.html
Hi There, I am trying to use IslatestMonth = 'Monthly Reports'[Date] =CALCULATE(MAX('Monthly Reports'[Date]), FILTER(ALL('Monthly Reports'),' ')). The issue I have with my second expression is the values are not same across my monthly reports. Is there a way I can use the above measure where I can set the latest date as true when I do not have a common/same secondary expression ?
Hi Kiran, do you still have this problem? It sounds like you'll need to wrap your second expression with an IF statement
What if I Want to automate monthly data but they're not the same format?
Besttt
Thanks!
Ey, do you have a video in which you compare always the last 2 months?
I have lots of data monthly, and I just need to find the variations between the last month and the current month... Basically I have the name of all my users (imagine 100), all the products by each user (I have 200 products, so each user has 200 rows), and I have the percentage they have advance in each product. Meaning in January Pedro can have 17% advance in Product 58, and in Febraury he can be also in 17% in the same product (he didn't use it), but in the case he use it, I need to identify the variation...
So my question is :P! do you have a tutorial for comparing always the last 2 months?
New columns: "islatemonth"! What happen if the "country" in the last file have some new countries which were not in previous files?
it's so distracting how you are switching the scenes too many times between the screen and yourself. People are busy getting the information with a short and limited time.
Userful. Like!
Great!