Category Archives: DAX

Introduction to Power BI Desktop – Recording Now Available

Thank you to everyone that attended this past Tuesday’s webinar hosted by Pragmatic Works called Introduction to Power BI Desktop. We had a great turn out! If you missed the webinar, you can view the recording here.

Check out these five Power BI tips!

There were lots of great questions and far too many to answer during the webinar so I thought I’d answer a few of the questions here.

Continue reading Introduction to Power BI Desktop – Recording Now Available

June 2016 Release of Power BI Desktop is Here!

POWER BI DESKTOPLast night at approximately 9:28 PM EST the June 2016 release of Power BI Desktop dropped, which you can download here.  The June update is pretty extensive and includes a bunch of new features and improvements. Here’s some of the biggest new features and some of my favorites.

Check out these 5 Power BI Tips

Continue reading June 2016 Release of Power BI Desktop is Here!

5 More Power BI Tips

I’ve had this blog post in my mind to write for the past month or so and I’m finally just getting around to it while I’m waiting to board my flight back home.

Check out my first 5 Power BI Tips here

These are just five more Power BI tips and tricks that I think everyone should know in order to get the most out of Power BI and produce better, more useful, and more powerful Power BI reports for their users. So without further ado, here are five more Power BI tips in no particular order. Enjoy!

Continue reading 5 More Power BI Tips

View Getting Started with Power BI and Time Calculations with Dustin Ryan is Now Available!

Earlier today I had the pleasure of speaking with the PASS Business Intelligence Virtual Chapter on getting started with Power BI and time calculations! There were a bit of audio issues on my end but thankfully we were able to still have a great event with lots of great questions!

If you missed the webinar, have no fear! You can still watch the recording right here. Just jump ahead to minute 11 as I had some unfortunate connection issues!

If you have any additional questions or feedback, please leave a comment down below.

Additional Resources

Download my PowerPoint slide deck here.

The PASS BI VC is a great group with tons of free, quality training events! I highly recommend you connect with this group so you can stay up to date on all their great events!

The PASS BI VC also has all their previous webinar recordings hosted on YouTube so definitely check that out!

If you’re new to Power BI, I suggest you start here.

Read more about Getting Started with R Visuals in Power BI

Read more about 10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

Read more about 10 DAX Calculations for your Tabular or Power Pivot Model (Part 2)

Feedback

I hope you enjoyed the webinar and that you maybe even learned a little something. If you have any questions, comments, or feedback, please leave it down below! Thanks for reading and watching!

10 DAX Calculations for your Tabular or Power Pivot Model (Part 2)

This is part two of my 10 DAX Calculations blog series. In this series I’ve blogged ten different DAX calculations you may find useful in your Power Pivot, Tabular or Power BI model. So if you missed part one, I would encourage you to check that out.

Read 10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

So here’s five more DAX calculations (in no particular order) that I hope you will find useful. Enjoy!

5. DAX Use an Inactive/Custom Relationships in a Calculation

In Tabular and Power Pivot models, you are limited to only one Active relationship between two tables. Often in a relational database you’ll have a role-playing table or a table that is related to another table with more than one foreign-key. A common example of a role-playing table is a Date dimension table since our fact tables often have multiple dates, which is what the following screenshot depicts. We can see that there are two relationships between the Sales and Date tables.

image

The solid line indicates that this is an Active relationship. The dotted line indicates that relationship is Inactive. In the Manage Relationships window, you can clearly see that one of the relationships is Inactive (highlighted in red). What this means for us is that during slicing, dicing and filtering the Active relationship is used.

The good news is that we can still build calculations that leverage Inactive or unspecified relationships in our model! To do this we can use the USERELATIONSHIP function. This function allows us to specify which columns to relate two tables together.

Here’s an example of a calculation for Total Quantity that uses the relationship highlighted in red in the above screenshot:

Total Quantity by Update Date:=CALCULATE([Total Quantity],USERELATIONSHIP(Sales[UpdateDate],'Date'[Datekey]))

 

So when we slice the Total Quantity by Update Date calculation by the Date table, the relationship used is the not the default Active relationship as is the case with the Total Quantity measure. We can clearly see this illustrated below in the pivot table:

image

4. DAX Semi-Additives Measures Opening Period and Closing Period Calculations

Sometimes we have a requirement to create a semi-additive type calculation. If you’re familiar with creating SSAS multidimensional cubes, you’re probably familiar with the concept of semi-additive measure. A semi-additive measure is a measure that is not fully aggregate-able meaning that to find the correct measure amount we cannot aggregate the measure across all time. Consider a measure for account balances or product inventory. If we wish to calculate the current product inventory for the year we must return the inventory on the last day of the year instead of summing the account balances for each day in the year.

There are a couple useful DAX functions we can use to find the opening period balance/inventory or the closing period balance/inventory: FIRSTNONBLANK and LASTNONBLANK.

Here is an example of an opening period type calculation. This calculation will return the first non-blank value in a given date range. Ideally, this will be the product inventory on the first day of the month or year.

Starting Inventory:=CALCULATE([Sum of Inventory], 
FIRSTNONBLANK('Date'[Datekey],[Sum of Inventory]))

 

And here’s a closing period type calculation. This calculation will return the last non-blank value in a given date range.
Closing Inventory:=CALCULATE([Sum of Inventory], 
LASTNONBLANK('Date'[Datekey],[Sum of Inventory]))

 

And here’s a sample of the query results:

image

Now I can easily calculate inventory growth rates!

Inventory Movement:=[Closing Inventory]-[Starting Inventory]

 

Inventory Movement Percentage:=DIVIDE([Inventory Movement],[Starting Inventory])

Pretty cool stuff!

image

3. DAX Previous Year, Previous Month, Previous Quarter Calculations

There’s a series of specific DAX functions that I think you’ll find useful for calculating the measures for a previous period:

PREVIOUSYEAR

PREVIOUSQUARTER

PREVIOUSMONTH

In part 1 we looked briefly how to calculate the previous MTD calculation, but these functions can also just be used to calculate the previous period metrics and compare the previous period to the current period.

Here’s a couple of examples of using PREVIOUSMONTH and PREVIOUSYEAR to calculate the Starting Inventory for the previous periods:

Previous Month Starting Inventory:=CALCULATE([Starting Inventory],PREVIOUSMONTH('Date'[Date]))

 

Previous Year Starting Inventory:=CALCULATE([Starting Inventory],PREVIOUSYEAR('Date'[Date]))

 

In the query results we can now see that we’re able to calculate the previous month and previous year starting inventory amounts. Excellent!

image

2. DAX First Day of Year, Quarter or Month Inventory/Balance Calculations

The previous examples in this post have all been calculations relative to the level of the Calendar Hierarchy being browsed, but sometimes we just need to calculate the measure on the first day of the year, quarter or month no matter what level we’re browsing. For those situations, we can use the following functions:

STARTOFYEAR

STARTOFQUARTER

STARTOFMONTH

Here you can see the calculations for the Year Starting Inventory and Quarter Starting Inventory:

Year Starting Inventory:=CALCULATE([Starting Inventory],STARTOFYEAR('Date'[Date]))

 

Quarter Starting Inventory:=CALCULATE([Starting Inventory],STARTOFQUARTER('Date'[Date]))

 

And now we have the Inventory Amounts for the first day of the year or quarter:

image

1. DAX Rolling Sum and Average Calculations

This type of calculation is easier to set in a multidimensional cube in my opinion but with that said I am more of a cube guy. To get this working, I first created a calculation that sums the total Sales Amount for each day in the past 90 days.

To do this I used the following functions:

DATESBETWEEN

FIRSTDATE

LASTDATE

DATEADD

And here’s the calculation:

Rolling 90 Day Total Sales Amount:
    = CALCULATE([Total Sales Amount], 
        DATESBETWEEN('Date'[Datekey],
            FIRSTDATE( /* FIRSTDATE gets the first date in this date range which will be 90 days ago */
                DATEADD( /* DATEADD lets me navigate back in time */
                    LASTDATE('Date'[Datekey]) /* This returns the last day no matter if I'm at the day, month or year level */
                    ,-89,Day
                )
            ),
            LASTDATE('Date'[Datekey]) /* Use the max date for the end date */
            )
        )

 

Review the comments in the code above to get a better idea of what all the functions are being used for.

Then I created a measure that counts the days that exist within the past 90 days. If the day I’m counting is the first day in my calendar that I have data, I only want to count that one day. This calculation is very similar to the previous calculation except I’m using the COUNTX function to count the rows returned.

Day Count:
    = COUNTX(
        DATESBETWEEN('Date'[Datekey],
            FIRSTDATE(
                DATEADD(
                    LASTDATE('Date'[Datekey])
                    ,-89,Day
                )
            ),
            LASTDATE('Date'[Datekey])
        )
    ,[Total Sales Amount])

 

Now that I have a calculation to get the rolling total sales amount and count the days all I need to do is simply divide the two numbers:

Rolling 90 Day Average Sales Amount:
    =DIVIDE([Rolling 90 Day Total Sales Amount],[Day Count])

 

And here’s the query results:

image

Notice that for January the Day Count measure shows only 31. This is because prior to January there’s not data so the Rolling Average is only considering days where data exists. Also notice that the numbers for March and Q1 match since both periods should have the same amount of sales and the same number of days.

Resources

10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

Watch this Power Pivot 101 webinar recording

Feedback?

Let me know your thoughts on these calculations! There’s 100 ways to skin a cat so if you have a different or better way to perform some of these calculations I’d love to see your solution! Thanks for reading! 🙂

Creating Time Calculations in Power BI

One of the current issues in Power BI is the inability to specify a Date table. The Date table is what enables us to create some of the powerful DAX time calculations like Year To Date, Month To Date and more when the Date key is not a Date data type. Ginger Grant has blogged about this issue with a proposed work around, which you can read about here. Even though we can’t exactly specify which table is our Date table in Power BI, that doesn’t mean we can’t create some nifty time calculations with Continue reading Creating Time Calculations in Power BI

10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

If you’ve read my blog for a while you may have seen the following posts:

Ten MDX Calculations For Your Cube (part 1)
Ten MDX Calculations For Your Cube (part 2)

Well the time has come for me to put together a compilation of ten useful DAX calculations for your Tabular or Power Pivot model (in no particular order so don’t infer any level of ranking or importance from the order they’re posted). Continue reading 10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)