Category: Excel

Adding Last Refresh Date and Time to Power BI Reporting

Posted on by Bb

A frequent question I have received is “How do I add the last refresh date and time to Power BI reporting?”. Great news, this solution is very simple! The code I provide in this article will allow you to create a source, and then use this in one or more of your visuals so that it’s always clear to you and your end users when the report was last refreshed.

Note: if you need refresh times for your individual sources, one solution would be to add a column with DateTime.LocalNow() and then work a DAX measure off of that once the data from that source is loaded for the first time. Definitely comment below if you would like me to expand on that.

Adding the Refresh Source

You are simply going to add a new source, choose Blank from the menu.

Click Advanced Editor to open up the M code window, then copy in the code below.

Power Query Code (M) – Power BI Last Refresh Date and Time

let
    Source =  #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}}),
    #"Added Custom" = Table.AddColumn(Source, "Updated", each "Updated "),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Updated]&Text.From([Date Last Refreshed])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Date Last Refreshed", "Updated"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "LastUpdate"}})
in
    #"Renamed Columns1"
Power Query Last Refresh Date and Time, Power Query M, Power BI Last Refresh Date and Time
Last Refresh Date and Time in Power BI M Language

Click Done. You will notice that I have added the word “Updated” ahead of the date and time in this code. I prefer to display the measure this way in my reporting. If you would prefer to just have the date and time, simply delete the steps after Source in the Query Settings at the right. I find it’s best to work from the bottom, up when removing steps.

Of course, you can also edit the output as you see fit, displaying in the way that works best for your report.

Power Query Last Refresh Date and Time, Power Query M, Power BI Last Refresh Date and Time
Power Query Settings and Steps

Adding the Last Refresh Date and Time to the Report

After Applying the change, you should now see the source and column listed.

Last Update

I find it easiest to add a card visual to display the date and time. Simply select the card visual and then select the LastUpdate column. You can then edit the visual as you see fit for your report.

Card Visual

Final Output

Last Refresh Date and Time Visual in Power BI

As always, I hope this post has helped you out. Please subscribe to be notified of the latest posts and leave a comment if you have any topics you’d like to see covered here. Thanks!

Topics covered in this post: Power Query Last Refresh Date and Time, Power Query M Current Date and Time, Last Refresh Date and Time in Power BI

code execution has been interrupted window

How to Make Excel Wait or Pause in a VBA Macro

Posted on by Bb

When running macros, I have often found the need for an Excel wait or pause, mainly so that other things can happen. DoEvents surely helps for waiting for things to happen within Excel, but sometimes you need to wait for processes to start or finish outside of Excel before moving onto other steps in VBA.

Why Wait?

I am a big fan of automating processes, especially with VBA in Excel. For example, I update and save a spreadsheet to SharePoint that a Power BI model is using. I have a Power Automate process set up that automatically refreshes the Power BI model whenever this particular spreadsheet is updated. The challenge is that Power BI temporary locks the workbook as it reads it, so if I am updating a few times in a short amount of time, Excel needs to wait until the SharePoint file is unlocked.

Also, I have another process where I call a Python script and once that is done, I continue on with VBA code to process the workbook Python outputs. I need Excel to wait while the Python code runs. Side note: let me know if you would like me to write more about these other automated processes. It’s simply amazing what you can get accomplished!

The Solution – Pause in a VBA Macro

Good news – making Excel wait is very easy! Here is the simple code:

Application.Wait (Now + TimeValue("0:00:50"))

This will have Excel wait for 50 seconds since the time is represented as (H:MM:SS). You can adjust as needed.

Bonus

In my code, I don’t need it to wait every time – only when it runs into an issue saving the workbook on SharePoint. Here is how I have set my code using an error handler in my VBA macro. 

On Error GoTo waitforupdate

lsc.SaveAs Filename:= _
        "https://sharepoint.com/sites/Reporting/Milestones/Status.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        
lsc.Close
Exit Sub

waitforupdate:
Application.Wait (Now + TimeValue("0:00:50"))
Resume Next

I hope this helps you! Please comment or reach out if you would like to see other topics covered. I truly enjoy writing about Excel tips and tricks, especially VBA and automation!

Addressed in this post (to help others find this):

  • Pause in a VBA Macro
  • How to pause Excel
  • How to make Excel pause
  • Make VBA pause
  • Make VBA wait
  • Make Excel wait
  • VBA code wait, VBA macro wait
  • VBA code pause, VBA macro pause
  • VBA wait for another application

Referencing an Excel Sheet With An Apostrophe in its Name – Solved!

Posted on by Bb

The Problem – Apostrophes in Sheet Names

You are trying to use a formula to reference an Excel Sheet with an apostrophe in its name and you keep receiving an error message or a #REF! reference error.

The Solution (Very Easy!)

Great news! Simply replace the apostrophe with two apostrophes in your formula. That’s right, two consecutive apostrophes, not a quote. Very simple solution indeed!

Below I provide two examples. You can manually update the name in your formula (or in the cell you are referencing if using an indirect lookup), or if working with many sheet names, you may opt to use the substitute solution shown below (formula displayed in E5). 

=substitute(A4,"'","''")

The substitute can be nested in an indirect formula also.

Excel Sheet with an apostrophe solution
Excel Sheet With An Apostrophe Solution

Please comment below on any other challenges you would like for me to cover. I am contemplating doing a quick solution series in this format!

Also, be sure to check out Dose for Excel (click the image below)! Add over 100 functions to Excel to increase your productivity and more!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!

Dose for Excel - +100 Functions

Some topics addressed in this post:
Ref Error in lookup
#REF! Error in lookup
Ref Error in formula
#REF! Error in formula
How to fix ref error in Excel from apostrophe
Apostrophe in Excel sheet name issue



My Poem – An Ode to Excel

Posted on by Bb

Written by Magic AI and I

Oh Excel, you’re a sight to behold
Your powers so vast, your secrets untold
Your formulas and charts, they never grow old
Your uses are vast, your features unfold

Your beauty’s unmatched, your power’s divine
Your data so precise, and figures so fine
From pivot tables to macros, you make life sublime
A spreadsheet maven, your use is so prime

Oh Excel, you make the mundane sublime
Your features so strong, all those bold lines
From tracking expenses to uncovering goldmines
You make the mundane task feel more like a sweet rhyme

Your power and ease, it’s hard to describe
Your talents so numerous, they stretch as far as the eye can see
You make the task of data entry so much less of a chore
So, Excel, my dear partner and friend, I thank you for making my work life, oh so much more

Excel Statistical Analysis for Business – Busn 210 – Introductory Video

Posted on by Annie D

Busn 210, Excel Statistics for Business class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in fall quarter, 2022.
To watch at YouTube here is the playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN

Copy/Paste Is Not Working While Remotely Working – Solved! (Remote Desktop Issue)

Posted on by Bb

I encountered a strange issue where I suddenly could not copy or paste in Windows or any of the Office applications (Excel, Access, Word, Outlook, PowerPoint, etc.) while working remotely. It was quite frustrating as I was pressed for time and needed this critical function to keep working. I then had the idea to stop the utility for the clipboard, effectively restarting it, and that worked!

If your clipboard copy/paste is not working on your remote machine, to save you the same time and frustration, here is how I resolved the issue:

  • Click on Start and type “Run”. Press Enter. (You can also press the Windows Key + R combination to open the Run window).
  • In the Run window, type cmd and press enter or click OK.
  • When the black command prompt window opens, type the following:
  • taskkill /f /im rdpclip.exe
  • Press enter.
  • Close the command prompt (black) window.

By entering this command, you are forcing the shared clipboard to close and restart itself. Hopefully this helps you out!

View Two Tabs at Once in Excel – One Workbook Side By Side

Posted on by Bb

Are you aware that you can view two tabs at once in Excel, side by side for a single Excel workbook/file?  This is especially helpful if you are working with a formula that affects another tab, whether checking for accuracy or troubleshooting.  It’s also great if you are running what-if scenarios such as in a sensitivity analysis and you would like to keep an eye on the values in two tabs.

Adding a second window in Excel to see two tabs at once:

  1. On the menu bar, click View.
  2. Click New Window.
  3. Position the two windows however you’d like.
  4. Note that it is still one file, so both windows will update when you make a change.  You can save from either window.

Alternatively, if you love keyboard shortcuts like I do, simply use this combination: ALT + W, then N.

Open Two Tabs in Excel
View menu for Excel

Here is an example of how it looks for this sample loan calculator file:

Open New Window Side By Side in Excel

Simple solutions are the best! I truly hope this helped you out! Please consider bookmarking this site and subscribing. I add new content often! Thanks!

Also, check out Amazon for some amazing resources: https://amzn.to/3IoE4O4!  Disclosure: this is an affiliate link, so I may earn a very small commission if you decide to make purchase.  Thanks for supporting my blog!

If I helped you today, please consider leaving a tip via PayPal to help cover blog costs! Thanks!!

Subscribe for more great content!

rank in Access

Solved: Pass a Parameter to a SQL Query in Power Query

Posted on by Bb

I’ve done a fair amount of research around dynamic and parameterized queries, specifically around an Excel query parameter, as in trying to pass a parameter to a SQL query in Power Query.  Here’s the very quick and easy way to do this!

This exact solution works in Excel. Creating a dynamic SQL query with a parameter in Excel allows for amazing flexibility. The use cases are endless and can benefit so many types of Excel work across industries – if you are querying any data source, such as a database, even an Access database, then parameterized querying in Excel can truly simplify your workflow. I am going to cover SQL queries specifically, but please comment below if you would like a tutorial on using a parameter with other data sources. Nearly anything is possible!

You can also easily use parameters in Power BI, but the process for creating the parameter itself is slightly different – I will cover that in a future post.  The query construction is the same in most cases though. Please note that this post assumes you are at least moderately familiar with SQL querying and Power Query. Your mileage may vary depending on your situation.

If you found this post because you are encountering a firewall error when incorporating a parameter, skip right on to that section below (click here) for the solution. After much trial and error, I have found a way that works to overcome this issue harnessing the power of query folding. 

The Solution: Passing a Parameter to a SQL Query

In my example, “SParameter” is the name of the parameter I am using, and it represents a store number (retail location identifier). The related field in my SQL table is [Store].

Create the parameter:

  • In any worksheet, add a new table, with a single record and column.  I highly recommend the Header clearly clarifies what the parameter is so there is no confusion in identifying it.
  • Add this table into Power Query by selecting the table, then Data > From Table/Range. 
  • When the query editor opens, if necessary, change the data type (in my case I need it to be text).  This is important!
  • Right click on the record and select “Drill Down”.  Since you have only a single record, this step will automatically create a parameter.  
  • In the properties, I name this SParameter (you may enter the name of your choice for your parameter).  Note that the name is case sensitive.

This is how the table is situated in my worksheet (1 is the parameter/the value I will change as needed):

This is how it looks in Power Query after all steps above were completed (I named the column from Enter Store: to Store to make it cleaner (this is an optional step):

Open or Add your SQL Server Query in Power Query

Add your query into the Advanced Editor.

let
Source = Sql.Database(“your connection name”, “your database name”, [Query=”

your query goes here

“])

Create the SQL query with your parameter:

Here’s my simple query example to demonstrate how to build the query with the parameter. Please substitute your table name and fields, as well as your parameter name.

With the parameter in the WHERE clause

“Select  * FROM StoreDetail.Store WHERE Store =” &SParameter&”“

  • Simply build your unique SQL statement in Power Query following my example above, adding your parameter into the query with quotes and the & (ampersand) sign surrounding the parameter name.
  • Check the results and modify as needed.
  • If your query has no additional clauses or criteria, simply put quotes and one & sign ahead of the parameter, and then one after followed by two double quotes to close the query statement.

 Additional criteria in the WHERE clause

Here is an example where I have additional criteria in mine, filtering on the OpenDate field; after the second quote, add a space and then continue on with the rest of your query.

Select  * FROM Storelist WHERE Store =” & SParameter &” AND OpenDate < GetDate()-365“

This is an example of the properly formatted parameter looks in the Advanced Editor in Power Query (I have built out my query further with the Group By clause – this is optional if it doesn’t apply in your case).

If you are running into issues with the query, you may need to add in single quotes surrounding the parameter (inside of the double quotes – “WHERE Store =‘” & SParameter & “‘;”), or possibly # signs surrounding your parameter inside of the double quotes, if your parameter is a date.  You may have to test with and without these depending on your unique scenario.  

If the query is successful, you can proceed with saving it or adding in additional steps in Power Query if you are further filtering or transforming the resulting data you pulled.

BONUS Information – if you are wondering what GetDate() is, this is the MS SQL current system date, so in a way this is technically a dynamic parameter as well.  In my case, the query will only return stores with an open date that is older than 365 days based on today’s date.  You may find this useful for your query as well if you are looking for dynamic dating.

After updating your parameter in the worksheet table, Select Refresh All from the Data menu and your updated query will run and return the results accordingly!

Managing Privacy Levels in Power Query/Excel

VERY IMPORTANT: If you are sharing your workbook with others, you may need to edit the Privacy Levels in order for the queries with parameters to work for them. Read on for these important steps.

In Excel Power Query, there are security measures in place to help prevent data from being shared unintentionally. When combining query sources by using the parameter (a value in your Excel file with a SQL server source), many times this security comes into play.

If other users are having an issue refreshing the query/queries where you have added a parameter into the SQL query, please have them follow these steps. I actually copy and paste this right into an Instructions tab of each of the shared workbooks containing parameters that I create for my clients and for colleagues.

  • Go to Data, click Queries & Connections.
  • Your query list will show up in the panel on the right.
  • Right click any query in the list and choose Edit.
  • A window will open. Click File, then Options and settings.
  • Choose Query Options.
  • Click Privacy, which is on the menu on the left, at the bottom.
  • Select the radio button for the Ignore the Privacy Levels and potentially improve performance option.
  • Click OK to save.
  • Click Close & Load in the Power Query Editor window (at the top) to exit the settings.
Parameter to a SQL query (privacy settings)

Troubleshooting/Alternate Method – Firewall issue

If you run into this dreaded error: “Formula.Firewall: Query ‘SParameter (2)’ (step ‘Filtered Rows’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”, then you will need to use this alternate method.

If you received the firewall error above, you can still use your parameter, but you will instead need to use the parameter as a filter in a subsequent step.  Now you may be possibly thinking – my table has thousands (or even millions) of records and I don’t want them all pulling in – don’t worry, this is the beauty of the query folding process when it works properly in Power Query.

Without the parameter in the WHERE clause

Select * FROM StoreDetail.Store WHERE OpenDate < GetDate()-365

  • Write your SQL statement in Power Query as noted above.
  • When the Query Editor returns the columns and record sampling, for the field you will be using your parameter, filter with any single value (Text Filters > Equals). This is simply a placeholder, to create the Power Query M formula for the next step.
  • You will now see that filtering in the formula bar. 
  • Replace the placeholder value with the name of your parameter, removing any quotes from the placeholder.  This is how it looks for me: = Table.SelectRows(Source, each [Store] = SParameter).
  • Power Query is smart enough to modify its native query to use the parameter, so it’s not going to pull in the millions of records and then filter after the fact.  Success!!  This is the power of Query Folding!

Why Can’t You Include the Parameter in the SQL Statement?

In the SQL statement, in most cases we will want add the store number/SParameter as part of the criteria in the select statement.  It may seem counter intuitive, but we cannot always successfully put the parameter directly into the SQL code/query. The challenge is that you are combining data sources and at this time, this is not allowed due to the built in security (SParameter portion is local, SQL portion is external).  Unfortunately some organizations have policies that disallow this. Sometimes it is an Excel version issue.

I truly hope this post helped you out.  If so, please feel free to leave a comment below letting me know so, and if you’d like, add what you’d like me to cover in a future post. Also, feel free to share this with someone else who may find it useful.

Do You Need Personalized Help and Custom Solutions?

If you get stuck or you would like to explore solutions and automation possibilities, please can reach out to me for help as I do offer consulting services as time allows.  I have over 20+ years’ of expert level experience delivering excellent, custom, strategic solutions in Excel, BI, Access, SharePoint and more. 

I have been called a guru and hero more times than I can count, as I am a great listener and truly have a knack for asking the right questions to understand unique business challenges. I am very passionate about crafting tools and processes that work for users of all levels and experience. 

Reach out today and let’s discuss how I can help you and your business!

I also offer one-on-one tutoring for customized learning and upskilling. Visit my consulting page and send a message if you are interested.

Other Excel Resources

Also, consider checking out some great Excel resources on Amazon!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to make a purchase, which will help cover the cost of hosting this website. 

Please bookmark and subscribe!  I am actively working on adding new, relevant content to help others out! Thanks so much!

Subscribe via Email and Stay Up to Date!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Buy Me A Coffee/Support the Blog 🙂

If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!

Thanks so much for supporting my blog and for helping others too!

multiply an entire column

Multiply an Entire Column by a Number in Excel (without using a formula)

Posted on by Annie D

Are you looking to multiply an entire column or range by a number? Get ready for a quick and easy tutorial!

You could always use another column and have the formula to do this, but this way is quick and easy if you do not plan on changing the values.

  1. All you need to do is have the number that you want to multiply in a cell (any cell).
  2. Now copy this cell and paste as special in the cells/range/column where you want to multiply it.
  3. In the Paste Special dialog box, choose the multiply option (in the Operation section) and click OK.  Your column/cells/range should be multiplied!
  4. If desired, can delete the cell that has the value which you multiplied by.

The resulting data is static/fixed so there’s no need to change any formula to value after multiplying using the paste special function.

Multiply An Entire Column

As always, I hope this helped you! Simple solutions are the best!

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

Subscribe for more great content!

Delete Every Other Row or Column in Excel (using Formula or VBA)

Posted on by Bb

Today we are going to explore various ways to delete every other row or delete every other column in Excel. VBA macro code is included to help you on your way if that is the path you choose.

Additionally, we will also cover how to delete every third/fourth/fifth row or column in Excel.

To delete alternate rows, you can use a helper column and use a formula that helps you identify alternate rows. This could be done by using the ISEVEN function that checks each row number and returns TRUE if the row is even and false if it isn’t.

Once you have this, you can easily filter the rows with FALSE in the helper columns and delete these.

In case you want to delete every third row, you need to use the MOD function to identify every third row. Once you have it, you can easily filter and delete every third row or every fourth row.

I also cover a method to use a simple VBA code to delete alternate rows in Excel.

Below is that VBA CODE TO DELETE ALTERNATE ROWS:

Sub Delete_Every_Other_Row()
Dim Rng As Range
Set Rng = Application.InputBox("Select the Range (Excluding headers)", "Range Selection", Type:=8)
For i = Rng.Rows.Count To 1 Step -2
If i Mod 2 = 0 Then
Rng.Rows(i).Delete
End If
Next i
End Sub

When it comes to deleting alternate columns, you cannot filter these. You can instead sort and bring all those columns together that you want to delete.

I cover a simple method that uses the MOD function to identify alternate columns and then sort these from left-to-right to bring these together. Once you have these in one place, you can select and delete these.

And there is also a VBA code that you can use to delete alternate columns.

Below is the VBA CODE TO DELETE ALTERNATE COLUMNS

Sub Delete_Every_Other_Column()
Dim Rng As Range
Set Rng = Application.InputBox("Select the Range (Excluding headers)", "Range Selection", Type:=8)
For i = Rng.Columns.Count To 1 Step -2
If i Mod 2 = 0 Then
Rng.Columns(i).Delete
End If
Next i
End Sub

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #ExcelTutorial

View on YouTube

Power Query Filter Rows by NOT Contains Criteria – Single Formula Solution

Posted on by Annie D

Learn how to filter a table based on NOT Contains Criteria. Download an example workbook here.

Easy step by step instructions below.  See a single formula solution using the functions Splitter.SplitByAnyDelimiter, List.Count and Table.SelectRows. Amazing formula solution from Power Query Poet, Bill Szysz.

Please watch the video if you’d like for a guided walkthrough and also another method that can be useful for multiple tables.  

Criteria Table

  1. Create a new, separate table with the list of terms you will want to exclude. Name the table “NoCriteria”.
  2. Add the excluded item (NoCriteria) table to Power Query – click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section.
  3. In the Power Query window, select the Transform menu and click convert to list.
  4. Under the File menu, choose Close and Load To, then choose Connection Only.

Building the Filtering via Power Query

  1. Add or create the list/table that will ultimately be filtered.
  2. Click any cell within the table that will be filtered. Add the excluded item table to Power Query (click within table, under Data menu, choose From Table/Range, which is in the Get and Transform data section).
  3. In the Power Query window, click the Add Column menu, and select Custom Column.
  4. In the window that opens, type this:

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))=1)

  5. Change [PRODUCT_NAME] in that text to your own column in the table that you will be filtering on if it is different.
  6. Select the statement you have typed in and copy it (you will need to paste this formula in a following step).
  7. Click OK. You will see that the formula you typed was changed by the program and a column was added.
  8. To change the formula back, click the menu bar, highlight the entire text and then replace by pasting in the formula you copied. Hit enter.
  9. The extra column should be removed and the table should be filtered on you criteria from the NoCriteria table.
  10. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

Any time changes are made to the exclusion list, you will need to refresh the filtered table. Simply right click any cell within the filtered table, and select Refresh.

 

Bonus – Filtered table with the excluded items only (not shown in video)

You can additionally create a filtered table that only includes the terms in your NoCriteria table!  

  1. To do this, go into Power Query.  Right click on your filtered table and click Duplicate.
  2. In that new table, you will very slightly change the existing formula in your Power Query to not equal one (see orange text):

    = Table.SelectRows(#”Changed Type”, each List.Count (Splitter.SplitTextByAnyDelimiter(NoCriteria)([PRODUCT_NAME]))<>1)

  3. Hit enter.  The table should now only filter on the items in your exclusion list, instead of including them.
  4. Click the File menu, choose Close and Load To, then choose where you would like the newly filtered table loaded to.

View on YouTube

FILTERING-OUT-CONDITIONAL-LIST-1-1Download

Create A Gantt Chart In Power BI With A Matrix Visual

Posted on by Annie D

Managing projects effectively requires clear visuals that communicate timelines and task progress at a glance. While Power BI offers many visuals, building a Gantt chart using the built-in Matrix visual is a powerful technique for showing detailed project timelines without needing custom visuals. This blog post walks through the key concepts and steps for creating a Power BI matrix Gantt chart, perfect for project managers and data analysts alike.

What is a Matrix Gantt Chart in Power BI?

A Gantt chart visually represents project schedules by showing tasks as bars mapped over calendar dates. Unlike using custom Gantt chart visuals, you can cleverly use Power BI’s matrix visual combined with DAX formulas and conditional formatting to mimic a Gantt chart. This approach is advantageous because it leverages native visuals without extra installation, is highly customizable, and integrates seamlessly with your existing data models and filters.

Why Use a Matrix Gantt Chart?

  • Native Power BI Visual: No need for third-party downloads or licensing.
  • Dynamic and Interactive: Integrates with slicers and filters for dynamic timeline updates.
  • Detailed Task View: Offers drill-down capabilities across project hierarchies.
  • Full Control Over Design: Customize colors and styles through conditional formatting rules.

Step-by-Step Guide to Creating a Power BI Matrix Gantt Chart

  1. Prepare Your Data Model
  2. Your dataset should include at least these columns:
    • Project or Task Name
    • Start Date
    • End Date or Duration(Optional)
    • Task Status or Category
    • You also need a Date table in your data model to serve as a timeline backbone.
  3. Establish Relationship sConnect your Date table to your Project table using the Start Date (and possibly End Date) fields to enable time intelligence across visuals.
  4. Create a Matrix Visual
    • Place Project Name (and subcategories if needed) in the Rows.
    • Place Date from the Date table in the Columns.
  5. Create a DAX Measure to Highlight Task DurationWrite a measure that returns 1 if a given date falls within the task start and end dates and 0 otherwise. This will be the basis for your Gantt bars.Example simplified logic: TaskActive = IF( SELECTEDVALUE('Date'[Date]) >= MIN('Projects'[StartDate]) && SELECTEDVALUE('Date'[Date]) <= MAX('Projects'[EndDate]), 1, 0 )
  6. Apply Conditional Formatting Use conditional formatting on the Matrix’s Values field. Format the background color to:
    • Show a distinct color (e.g., gold or blue) when the TaskActive measure equals 1. Show a lighter or neutral color when 0.
    This creates the visual bars of the Gantt chart within the matrix cells.
  7. Enhance Your Visual
    • Turn off subtotals for clarity.
    • Use slicers to filter projects or timelines dynamically.
    • Add tooltips with task details.
    • Customize colors by task status or category using additional measures and formatting rules.

Benefits of Using a Matrix Gantt Chart in Power BI

  • Cost-effective: No licensing or external visuals needed.
  • Integrated experience: Aligns with your Power BI reports and dashboards perfectly.
  • Scalable: Works well from small projects to complex multi-phase portfolios.
  • Customizable: You control colors, interactivity, and granularity.

By leveraging Power BI’s matrix visual combined with smart DAX measures and conditional formatting, you can build a robust, customizable Gantt chart tailored to your project management reporting needs. Start experimenting with your project data today and unlock richer timeline insights right inside Power BI!

Other Options/Resources

For those interested in alternative approaches, there are also custom Gantt chart visuals available in Power BI Marketplace, but the matrix method provides unmatched flexibility and control for many project reporting scenarios.