PERCENTILE.EXC and PERCENTILE.INC differs only in the way the possibly-non-integer rank is calculated. For PERCENTILE.INC (and PERCENTILE) the calculated rank is K*(N-1)+1. For PERCENTILE.EXC the calculated rank is K*(N+1). Percentile Percentile is a statistics term used to describe the Nth observation in a data set after sorting in ascending order. For example, 40th percentile (P40) Read More
Category: Microsoft Family
Microsoft Windows Servers, AD, Exchange, Azure, Windows 10, Office 365 and more
How to Calculate Week-Start Date and Week-End Date
What’s the easiest way to calculate your weekly metrics when your data has a daily granularity? Simply create a new calculated column for the “week start date” or “week end date” to use in your visuals! This is very easy to do, but maybe a little counterintuitive to the new Power BI user. Let’s dive Read More
How to Combine CSV Files by Using Power BI
If you are finding a solution for Excel, please read this post. https://summalai.com/?p=3964 Below by Stephanie Bruno One of the unsung heroes to me in Power BI desktop (or Power Query in Excel) is how wonderfully simple it is to combine csv or Excel files from a folder. Maybe it’s not totally unsung, but I Read More
How to Fix “A function ‘CALCULATE’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.”
Sample problem: I’m looking for a more elegant way sum based on a prior business day calculation. The following *does work* but will get a little more cumbersome when doing MTD or YTD calculations: Prior Day Invoiced = CALCULATE(sum(‘Sales Ledger Transactions'[Net Sales]),’Sales Ledger Transactions'[Fact Attributes.Source Sale Type Code] = “INVOICED”,‘Sales Ledger Transactions'[Date.Date] = (if(weekday(today(),1)=1,today() – Read More
How to Create Forward Forecasts
In this blog, we’re going to go over how you can create automated forecasts from historic data in Power BI. This is common in businesses to make forecasts and budgets. You may watch the full video of this tutorial. Now, have you ever wanted to create automatic forecasts based on historical information? In the past, this was relatively difficult to Read More
Import Data From a Folder with Multiple Files (Excel)
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Use Power Query to combine multiple files with the same schema stored in a single folder into one table. For example, each month you want to combine budget workbooks from multiple departments, where the columns are the same, but the number of rows and values differ Read More