We can also perform time intelligent functions on our date fields in DAX.
Let’s compare this year’s sales to last year’s sales. We will create a new measure for previous year sales.
First, let’s create a date table. This is created in Power BI Query Language (M):
Next, create the measure. We are calculating the sum of the extended price, in the previous year, using the PREVIOUSYEAR function:
What this means, is in our dates table, Power BI will look for the first date in the date table, and use that as input in the PREVIOUS calculation. What we have selected in our date table will determine how this measure will work.
Next, let’s drag across the Year as a slicer, and our new Previous Year Sales measure:
As you can see, the measure if displaying blank. We have calendar years going back to 2014 but no data for that period. Let’s select 2017:
We now get the amount for 2016 being displayed, as per our data. If we select 2018, we will see 2017 data:
If we bring across the Extended Price from the order lines, and create a new visual with our measure, we can see side by side the values compared. Our Extended Price field will filter based on the year selected:
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
