We want to judge whether or not we are tracking the monthly budget on a daily basis. Therefore, we need to compare the running total of the monthly revenue with the running total of our monthly budget at the day level.
We start by calculating a monthly running total for our revenue, Running Monthly Revenue. This formula starts by simply getting the current date out of our Date column in the R07_Revenue table and storing this value in the __Date variable. We can then use this __Date variable to calculate the MONTH, __Month, YEAR, and __Year values of that date. Using these variable values, we can compute the running total of revenue for a month by using the ALL function to return all of the rows in the R07_Revenue table and then use the FILTER function to filter these rows to only the rows that have a YEAR value that equals our current year, __Year, have a MONTH value that equals our current month, __Month, and have a value in the Date column that is less than or equal to our current date, __Date. Then, we can use the SUMX function to sum the values in the Revenue column to iterate over these filtered rows.
For comparison purposes, we also need a running budget figure for each day, Running Monthly Budget Per Day, which is similar to the formula for Running Monthly Revenue. We start by simply getting the current date of our Date column in the R07_Revenue table and storing this value in the __Date variable. Then, we can use this __Date variable to calculate the MONTH, __Month, YEAR, and __Year values of that date and the DAY value, __Day, of that date. Then, we can use the __Month and __Year variables to extract the corresponding budget, __Budget, for the current month and year from the R07_Budget table using the FILTER and SUMX functions.
Finally, we need to calculate the number of days in the current month, __Days. We can do this by using the DAY, EOMONTH, and DATE DAX functions in conjunction with one another. The explanation of this calculation is that we create a DAX date by using the DATE function and specifying our __Year, __Month, and the first day of the month, 1. The EOMONTH function returns the last date of the month given a date and an offset specified in the number of months; in this case, 0. Thus, EOMONTH effectively returns the last date of the current month and we use the DAY function to get the value of that DAY, such as 31 for January.
Now, we can return our budget, __Budget, multiplied by a percentage of the current day versus the total days in the month to spread our monthly budget evenly over the number of days in each month.