This article is for companies that do NOT want to move to the new and improved Power BI template.

Click here to go to the new PBI tutorial. The new file does not have to be updated each year.

There are a couple things you’ll need to do to update your PowerBI file for the new year.

  1. Update the URLs that are restricted by a date range. (See video – how to change URLs with date ranges).
  2. Update your calendar table.
  3. Start on “Dashboard” tab and create new ‘measures’ for 2022 and swap them out with older measures. (See video – How to change first 2 measures in Dashboard).
  4. Change the “3 Yrs” tab (See video with same name).
  5. Change the ‘visuals’ in the “% Change” tab with newly created ‘measures.’ (See video – % Change tab).
  6. Go through the remaining tabs to update the rest for 2022. (See video 5 – Final -recap).

1. Update all URLs with a date range

You will need to update the activityJournals, commissions, opportunities, and sales URLS in your file.

INSERT VIDEO 1 HERE

2. Change calendar table date range

You will need to click on the “Calendar 1” table and update the date range.

  1. Click on “Calendar 1” on the field list on far right.
  2. Click on the arrow to expand the formula field.
  3. Change the dates to start in 2018 and end in 2022. Hit “enter” key on keyboard to save your changes.

3. “Dashboard” tab

We are basically going to be copying/pasting and changing all existing ‘measures’ that have a calculator icon on the left and have a year in their name.

You will create 2 new “measures” and swap them out with the old ones. These are both in the “Commissions” table. Below are the 2 new ones, you can copy and paste from here if you like.

Sales (comms) YTD 2022 =
CALCULATE(
SUM(Commissions[Sales Amount]),
DATESBETWEEN(
‘Calendar 1′[Date],
DATE(2022,1,1),
DATE(2022,MONTH(TODAY())-1,DAY(TODAY()))))

Comms YTD 2022 =
CALCULATE(
SUM(Commissions[Commission Amount]),
DATESBETWEEN(
‘Calendar 1′[Date],
DATE(2022,1,1),
DATE(2022,MONTH(TODAY())-1,DAY(TODAY()))))

INSERT VIDEO 2 HERE

4. “3 yrs” tab

In this tab you will create measures in the “Sales” table. You’ll need “Sales YTD 2022” (you may find that some measures are missing for 2021. Create m if you need them).

Sales YTD 2022 =
CALCULATE(SUM(Sales[Sales Amount]),
DATESBETWEEN(
‘Calendar 1′[Date],
DATE(2022,1,1),
IF(YEAR([Last Invoice Date]) = 2022,
0,
DATE(2022,MONTH(LASTDATE(Sales[Invoice Date])), DAY(LASTDATE(Sales[Invoice Date]))))))