Showing posts with label DAX. Show all posts
Showing posts with label DAX. Show all posts

Wednesday, November 18, 2015

Rolling 12 Months Chart with PowerPivot

Once again, work has provided me with a great article topic!  A request recently came across my desk to create a rolling 12 & 24 months average.

Here is how to do it:

First thing is first: You will need to have the “Last Updated” Table&Column in your Model.

 If you can connect to a SQL Server database, I create this by adding a table "Last Updated" with the following SQL as source (This also tells the users how new the data is):

Select GetDate() as ‘Last Updated'

This method is preferred to simply using the Today() formula in a linked table because your source data may not be refreshed daily.  For example, we have some financial dashboards that are only refreshed once per month on the first day of the month.

Next, create a calculated measure on the "Last updated" table with the following formula:

LastUpdatedMeasure:=sum([Last Updated])

Next, on your Fact table that you are going to use to create the rolling average (or whatever rolling metric you desire), add the following Calculated Measures to determine start and end dates for the rolling year ([last updated date] - [12 months]):

RollingYearEnd:=EOMonth([LastUpdatedMeasure],-1)
RollingYearBegin:=1+edate([RollingYearEnd],-12) - You can make this 2 years by changing "-12" to "-24".

Finally, use the following calculate formula to run whatever calculation you are looking for within that rolling year. This is the measure you will use as the “Value” on your chart or pivot table.

The formula below has multiple parts that I will explain:

=Calculate(expression, filter1, filter2..)

=Average(Column)

=Filter(TableName, TableName[Filter Criteria])

"&&" - This allows you to use an "AND" in your Filter Criteria.  Example: Event Date is >= RollingYearBegin "AND" Event Date is <= RollingYearEnd  - This ensures your data is between the start and end date. You can also include a column to qualify the event to be included by simply adding another "&&"

RollingYear_Avg:=calculate(average([ColumnToBeAveraged]),filter(FactTableName,FactTableName[EventDateColumn]>=[RollingYearBegin]&&FactTableName[EventDateColumn]<=[RollingYearEnd]&&FactTableName[QualifyingEventColumn]="Y"))

The final part of the formula &&FactTableName[QualifyingEventColumn]="Y" was required in my case because not all of the rows in my dataset qualified to be included in the average.  This is not required to make this formula work.

So, now that you are an expert in rolling year formulas, go out and impress the world!

Tuesday, March 18, 2014

PowerPivot: BLANK() & ISBLANK()

Today I ran into an issue when writing a formula in the calculated column area.  I needed to calculate the difference in minutes between two date/time columns.  However, I first wanted to check to be sure both columns in the row had an entry to avoid some outrageous number appearing as the result of the formula.

In excel I would have simply handled this issue with an If() Statement check to be sure the cell wasn't blank and then returning "" if it was.

The formula in an Excel spreadsheet looks like this:

=if(A1="","",if(A2="","",(A2-A1)*24*60))

Unfortunately, when used in the data model this will return "#Error" for all rows in the column instead of a blank value.

After consulting with a coworker, he showed me how he has resolved this issue:

=If(ISBLANK([Column1]),BLANK(),if(ISBLANK([Column2]),BLANK(),([Column2]-[Column1])*24*60))

The ISBLANK() formula simply checks to see if the value in the column is blank.  If it is, BLANK() returns the value as a blank cell - Much the same as using "" in an Excel Sheet formula.

To Explain the formula, I will write it in words:

If the value on this row of column1 is blank then return blank, otherwise go check if the value on this row of column2 is blank.  If it is, return blank, otherwise calculate the difference between Date2 and Date1.  Multiply the result by 24 to get hours.  Multply the hours by 60 to get minutes.

Hope this helps someone else save some time!