One of the often required calculations for users in reports is to be able to calculate a point-in-time closing balance, which is especially relevant when dealing with stock levels or financial data.
In the Analysis Services world, at least in the Enterprise Edition, we’re lucky that we have a variety of Aggregation Functions that can deal with semi-additive measures such as a closing balance. Or we can always turn to MDX if we need to which will achieve the same thing.
In PowerPivot, we don’t have Aggregation Functions, but there is DAX, which has a variety of time intelligence functions up its sleeve. The idea behind this post is to see how PowerPivot would deal with the requirement of reporting a closing balance measure.
DAX Time Intelligence Functions
One of the DAX functions that we can use for our closing balance is called ClosingBalanceMonth(), which will simply evaluate an expression at the end of a given month. There’s also the similar ClosingBalanceQuarter() and ClosingBalanceYear().
Having noticed these functions within PowerPivot, my first port of call was to set up a simple PowerPivot model by taking data from the FactInternetSales table in AdventureWorksDW, plus all it’s related dimensions of course. What I ended up with was the following simple pivot table in Excel 2010:
Sales Amount obviously isn’t a semi-additive measure, but I’m treating it as one in this example. My first port of call was to use ClosingBalanceMonth() to try and get the value for the month to be equal to the value for last day in the month. I managed to come up with the following formula:
=CLOSINGBALANCEMONTH(Sum([SalesAmount]), ‘DimDate'[FullDateAlternateKey], ALL(‘DimDate'[FullDateAlternateKey]))
This produces the results that are shown below, in the measure called ClosingBalanceFormula:
It does a good job of calculating a closing balance for each month, but it presents that closing monthly balance on each day. Therefore, I started looking for an alternative approach and found the LastDate() function. It returns the last date that’s in context for the passed date column. The last date that’s in context at the month level will be the last date in the month, but thankfully the last date that’s in context at the day level will be the current day. Therefore I came up with the following formula:
=’FactInternetSales'[Sum of SalesAmount](LASTDATE(‘DimDate'[FullDateAlternateKey]))
This produced the results that I wanted – the daily closing balance at the day level and the monthly closing balance at the month level:
A bit more digging lead me to find out that a combination of the Calculate() and LastDate() functions gets to the same result, as an alternative. That formula is:
=CALCULATE(Sum(‘FactInternetSales'[SalesAmount]), LASTDATE(‘DimDate'[FullDateAlternateKey]))
Conclusion
It’s a shame that the ClosingBalanceMonth() formula didn’t quite work, but perhaps getting that approach to work will come out in the wash with more DAX experience. However, as with MDX or any kind of expression language, there’s always going to be a load of different ways to get to the same result. It’s much more efficient to work knowing that you’ve got several ways around a particular problem, rather than being forced down a particular path, and in fact, I think the range of calculation functions available shows the richness of the DAX language.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr