This post is an extremely practical one targeted at business analysts. Most of you have a scenario that includes a general ledger and monthly budgets that have corresponding actuals. Often there are actuals in the system that you don’t want to include because they haven’t “closed” yet.

If you are somewhat new to Power BI, you are probably handling this by manually setting filters in your Power BI report and having to adjust them each month. When i see report authors try to automate this i typically see a single measure including a FILTER expression to account for ONLY closed actuals. The problem with this approach is that you end up writing this FILTER expression many times over if there are many measures that need to observe closed vs unclosed actuals. And this approach also doesn’t allow for adjusting axis on bar charts displaying your budget and actuals values.

In this blog post I will show an alternative option that is pretty easy and allows it to be applied to any measure that you may include in your model. For the PBIX file referenced below, you can find it on my GitHub

In the report below, notice that my main “% Variance to Budget” measure on the left side is including unwanted actuals from 2016, a few months in 2017 and 2018. I am using the same “% Variance to Budget” measure on the right side but is only including months that have a budget defined (2017 only) as well as ONLY months that actuals have closed (January through October excluding November and December).

The two visuals on the right are utilizing a calculated date table based on our “closed actual dates” instead of the full date dimension. Lets review the simple data model being utilized in this example.

I have a budget table that includes the status if that month has been “closed” or not.

Date	Budget	Status
01/01/2017	300	Closed
02/01/2017	500	Closed
03/01/2017	500	Closed
04/01/2017	400	Closed
05/01/2017	400	Closed
06/01/2017	500	Closed
07/01/2017	500	Closed
08/01/2017	400	Closed
09/01/2017	400	Closed
10/01/2017	400	Closed
11/01/2017	400	Open
12/01/2017	500	Open

 

Notice that it does not include any dates from 2016 or 2018 but also shows the status of November and December as “Open”.

Now in my actuals table, you can see that the dates include 2016, November and December of 2017, and 2018.

Date	Actuals
01/01/2016	800
03/01/2016	100
09/12/2016	4000
11/22/2016	250
01/04/2017	100
01/28/2017	300
02/01/2017	500
03/12/2017	200
04/05/2017	400
04/22/2017	100
05/02/2017	300
05/28/2017	100
06/29/2017	500
07/01/2017	100
07/04/2017	800
07/23/2017	200
08/09/2017	400
09/21/2017	100
09/23/2017	500
10/11/2017	300
10/20/2017	100
10/31/2017	250
11/12/2017	100
11/21/2017	200
12/01/2017	500
12/22/2017	2000
12/30/2017	100
01/02/2018	200
02/02/2018	1000

I have pulled both of these tables into my data model and have also included a Date dimension table that i typically use. However,  have also created a “calculated table” from the “New Table” icon on the modeling tab that is going to reflect our “closed actuals dates”.

The formula for this calculated table is below:

ClosedDates = CALENDAR(
    MIN(Budget[Date]),
    CALCULATE(EOMONTH(MAX(Budget[Date]),0),Budget[Status]="Closed"))

 

The CALENDAR() DAX function expects two dates that it will use as the beginning of the date range and the end of the date range that will be produced. All dates between these two dates will be produced as a table.

The “Start Date” is pretty easy as we will use the minimum date from the budget table “Budget[Date]”. The “End Date” however is a bit more complex. A Calculate statement is used so that we can apply a FILTER to the table to ensure the Budget[Status] is “Closed”. This is the expression is the second parameter to the CALCULATE function. The first parameter is doing two things:

• First it is getting the MAX Budget Date which in our budget table after the filter for status is applied would be 10/1/2017. The problem is that our budget is monthly while our actuals are daily. The monthly budget dates are being stored as the first date of the month.
• So, the second function being applied is EOMONTH which stands for “End of Month”. This will give the last date in the month based on a date value in its first parameter and an offset (how many months from the date that is given) in which our case we want to use the same month so the offset is zero.

 

Now we have a calculated table that includes ONLY dates we want to use when comparing actuals vs budget. In the data model, lets connect this to the Date Dimension table as that is the table that would likely hold the relationship to the Actuals and Budget table.

You can see from my model i did add two additional calculated fields to the ClosedDates table for Month and Year. Those formulas are below:

Month = Month(ClosedDates[Date])

Year = Year(ClosedDates[Date])

 

To test this, i used a percentage difference formula that i created from a “Quick Measure” by clicking the “…” next to a table you want to create the measure in.

I named mine “% variance to budget” which automatically produced the following DAX expression:

% Variance to Budget = 
VAR __BASELINE_VALUE = SUM('Budget'[Budget])
VAR __VALUE_TO_COMPARE = SUM('Actuals'[Actuals])
RETURN
 IF(
  NOT ISBLANK(__VALUE_TO_COMPARE),
  DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
 )

 

Now that the measure is complete, add a card visual displaying it.

Initially you will get a number that contains all actuals being used as the comparison value to the budget, but by simply dragging into the FILTERS pane your ClosedDates[Date] field as a filter in the visual or even at the page level (if everything on this page is comparing actuals against budget) equal to “is not blank” it will ONLY show actuals for dates that are closed and a budget is defined.

Now your measure is only showing variance based on the dates that exist in the Closed Dates calculated table

Because we created a calculated table, it can also be utilized in a bar chart showing our “actuals” and “budget” fields without having to wrap them in measures or manually filter out the time frames that don’t have budget associated or closed actuals.

 

Conclusion

Not the flashiest blog post but I hope can help solve a real practical problem for many business analysts trying to compare budgets vs actuals or even forecasts.