Learn Power BI

DAX Practice: PREVIOUSYEAR and SAMEPERIODLASTYEAR

Monday, June 21st, 2021

We want to set the Units sold to be 10% higher than the prior year.

Open the Sales worksheet from Sales and Product Prior Year

Create a date table

Date = CALENDARAUTO()

On the modeling tab, relate the tables. This is necessary so that we can use Data[Date] as a filter for Sales.

Create a Goal measure

Goal = CALCULATE(sum(Sales[ Units]),PREVIOUSYEAR('Date'[Date]))*1.1

The 2019, 24 units were sold, so in 2020 our sales goal is 24 x 1.1 = 26.4.

Here’s another option:

GoalV2 = CALCULATE(sum(Sales[Units]),SAMEPERIODLASTYEAR('Date'[Date]))*1.1

Download Workbook

DAX Practice: RankX

Friday, June 18th, 2021

We have two tables, Sales and Product:

Loading the data into Power BI, the two tables are related by ProductID.

Now create a DAX Measure:

Rank ProductID By Units = RANKX('Product',sum(Sales[Units]))

Here’s a table with the new measure and ProductID. But notice that the Rank for all rows is 1. This is because of the query context. The context is the current ProductID, so since the context only includes the the product ID in the current row, the rank is always 1.

So that didn’t work! Here are two ways that do work:

Rank = rankx(ALL(Sales[ ProductID]),calculate(sum(Sales[Units])))

Why use calculate? In order to transition from row context to filter context. Another way to get filter context is to use a measure:

Sum of Units = sum(Sales[Units])

Rank using Measure = RANKX(ALL(Sales[ProductID]),[Sum of Units])

ALL(Sales[ProductID]) removes the ProductID from the context so that the rank can span all four or our ProductIDs. Ranking is done on the Sum of Units meaure.

Sales and Product

Download the workbook – RankX Practice

 

Getting Sum of Last Available Date in Month

Thursday, May 20th, 2021

Suppose you want to know how many items were purchased on the the last business day of the month. For example, here we see that 41 items were sold on the 30th of January, but no items were sold on the 31st.

You can use LastNonBlank to determine the last day of the month items were sold.

LastNonBlank = LASTNONBLANK('item purchased'[Date],sum('item purchased'[Quantity]))

Creating a table with this expression, we see that 1/31 is not shown because there are no rows on this date. The view would also show 1/30 if there were rows for 1/31 with blank values for Quantity.

Now let’s use LASTNONBLANK to get the end of month total:

Quantity Last Day of Month = calculate(sum('item purchased'[Quantity]),LASTNONBLANK('item purchased'[Date],sum('item purchased'[Quantity])))

Sum of quantity sold on 1/30 was 41.

Download workbook here.

DAX practice: Counting the number of items selected on a slicer

Friday, May 14th, 2021

Load the purchase data into power bi. The data model should be created automatically, but I did have to select ‘use first row as headers’ for the item table:

Create a slicer with Item

Then create a DAX measure that to count the number of items selected on the slicer

Items Selected = DISTINCTCOUNT(items[Item])

I created a card showing the value of Items Selected. Here’s my view… since I have bananas, melon, and papaya selected the view shows 3 items selected.

Download workbook.

Create workbook visual showing MOM % change

Monday, May 10th, 2021

Open a new workbook and connect to the shipping cost data,

Create a date table:

The CALENDARAUTO function will create a table with a single column named “Date” that contains a contiguous set of dates based on the dates already in the model… 1/1/2020 is the first date in Shpping_Cost and 11/9/2021 is the last date, so the range of dates will be from 1/1/20 to 11/9/21.

Add a table to the view:

Create the relationship:

Add shipping cost, Month & Year to the view:

Sort by Year

In the Values area, move Shipping Cost after the date fields:

Now add the % MOM calculation. The variable “PriorShipping” to determine the shipping cost from the previous month:

% MOM Shipping Cost Change = 
var PriorShipping = CALCULATE(sum('Shipping Cost'[Shipping_Cost]),PARALLELPERIOD('Date Table'[Date],-1,month))
return divide(sum('Shipping Cost'[Shipping_Cost]) - PriorShipping,PriorShipping)

Adding the new measure to the view, we should see something like this:

A few things to fix here:

1. Sort on Year then Month
2. Change the format to a percentage
3. Hide the total row for % MOM Change

1. Sort on Year then Month

2. Change the format to percentage

Switch the the model tab, select the % MOM change field, and change the format using the dropdown.

3. Hide the total row for Percent Change

This can be done by modifying “% MOM Shipping Cost Change” to only return the % change when the ISFILTERED function returns true.

% MOM Shipping Cost Change = 
var PriorShipping = CALCULATE(sum('Shipping Cost'[Shipping_Cost]),PARALLELPERIOD('Date Table'[Date],-1,month))
return if(ISFILTERED('Date Table'[Date].[Month]),
    divide(sum('Shipping Cost'[Shipping_Cost]) - PriorShipping,PriorShipping)
)

Download to Power BI workbook

Link to shipping cost data

Create a Power BI visual showing current month next to same month from prior year

Friday, May 7th, 2021

I created sample data with a date column and a numeric column

Load the data in Power BI and create a table showing shipping cost by month:

We need a date table. Create one here:

Use this DAX expression to create the date table:

Date Table = CALENDARAUTO(12)

Link the tables:

Relationship should be many to 1 (*:1)

Then add a new DAX measure:

Prior Month SPrior Month Shipping Cost = CALCULATE(sum('Shipping Cost'[Shpping_Cost]),PARALLELPERIOD('Date Table'[Date],-1,month))

Add the new measure to the view:

Sort ascending on year:

You can see that March 2020 shows $4.24 for the Shipping cost. April 2020 shows $4.24 for the prior month shipping cost.

Look what happens if use use the date from shipping cost instead:

Prior Month Shipping Wrong = CALCULATE(sum('Shipping Cost'[Shpping_Cost]),PARALLELPERIOD('Shipping Cost'[Date],-1,month))