PowerPivot : Showing the latest data

Introduction

Show me the latest information that you have! This is a common requirement when you are building reports for users. Users do not always want to click on the selections or slicers (in case of a Excel workbook) in order to select the last day or last month. Just show the latest information.

For this blogpost, I've used an example for my data warehouse monitoring system, and this data warehouse monitoring systems monitors the SSIS packages that were executed. I have structured the SSIS packages into 3 levels: Environment, System and Package. For instance Sales is the environment, Staging and Mart are the systems and the SSIS ETL packages are at Package level. Now in order to know whether the data is correctly loaded for the different areas, the monitoring happens at Environment level.

The DAX Expressions

First create an expression that determines the latest loading date of your data warehouse.

       
MaxDate:=CALCULATE(MAX(Environment[EnvironmentRunTime]);ALL(Environment))

For instance this will give you this : MaxDate: 20160601

Now, add a calculated column (not measure) to the table. This will be used as the filter for the table.

       
=IF(Environment[EnvironmentRunTime] = [MaxDate]; "Latest"; "")

       

In order to prevent using a filter (dropdown, ugly) in Excel, you can use the filter in the CALCULATE Expression.

       
FilterMaxDuration:=CALCULATE([SumDuration]; Environment[ISLatest] = "Latest")
FilterMaxRecordsNew:=CALCULATE([SumRecordsNew]; Environment[ISLatest] = "Latest")

And now it's possible to use these measures in a Pivottable in a Excel workbook :

Conclusion

Showing the latest information is a nice trick that you can use in your PowerPivot Dashboards.

Greetz,
Hennie