The following is a DAX formula you can use to create a calculated column that shows “next year’s” value in “this year’s” row. You can easily adapt this to show “yesterday’s” amount or “tomorrow’s” total as well. It can be modified for days, weeks, months, etc. as long as the time measure is able to be sorted sequentially.
Automatically create and send reports, files and lists on a regular schedule using Microsoft Flow’s recurrence trigger. Whether hourly, daily, weekly or monthly you can deliver the most current and relevant data from SharePoint or OneDrive to interested parties via email without lifting a finger. Combine this with calculated columns in SharePoint and conditions for some awesome possibilities:
|Report costs or expenditures above a certain amount
||Current month’s birthdays and/or workiversaries to your secretary
||Send expenses per department or individual to that department or individual
||Budget and salary or payroll figures weekly
|Notify when an open ticket is idle for a week or incomplete
||Upcoming events per location
||Share evaluation status with supervisors for just their employees
||Recently closed deals and contracts
|Survey responses or reviews under 3 stars
||Upcoming deadlines per department
||Client info and updates to proper salespeople based on location or product
||Distribute new hires’ contact/location info to the organization in weekly batches
Even if your SharePoint site’s regional settings are correct (or whichever data source you’re pulling from), Power BI could convert it to the wrong time zone upon import. It’s a quick fix, luckily. Instead of using your “modified,” “created” or other date field in your report, we’ll create a new calculated column in Power BI to use with an accurate time zone.
Note: This, like many O365 things, is rapidly evolving. If you’re aware of better practices or new updates to licensing, feel free to mention it in comments.
I’m currently at SharePoint Fest Seattle where Chris McNulty, Sr. Product Manager for Office 365 and SharePoint at Microsoft, mentioned (as I understand) there could be changes coming to licensing that would allow more people to consume Power BI reports in a friendlier (more affordable) licensing structure. This would be amazing because currently:
I can create reports. People can’t view data in those reports in a secure way because the entire organization isn’t licensed for Power BI per person above the “free” license.
Specifically I, with a Power BI Pro license, can create reports and place those in SharePoint’s new page experience Power BI web parts (in Preview) but other people (with free or without Pro licenses) cannot view them. They see the following:
Of course, to me as the creator and properly-licensed individual, I see the report perfectly embedded as it should be. And not every organization can afford to license every single user appropriately to be able to simply view embedded reports. Especially if consuming reports (not sharing or building) is the only function they need in the Power BI realm.
In this post, I’ll cover:
- How to embed Power BI reports the normal, easy (but license-exclusive) way
- Why the webpart (normal, easy way) is cooler than embedding a script
- How to embed the report in a (less secure) way so that non-licensed or free-license individuals can actually view and manipulate the data
One of my favorite features of Power BI is the ability to have published reports automatically refresh data on a schedule. This is great for “setting and forgetting” your reports, knowing wherever you publish them they will be showing the most recent data for your clients. I feel like it used to be depending on your license, you could be limited to how frequently you can refresh (max of once per day), but you can refresh nonetheless. And this may have changed, as I couldn’t find (in my brief search) any confirming statement.
Let’s set up that scheduled refresh!