Search code examples
powerbidaxpowerpivot

MTD Budget with Fiscal Calenar

I have a simple budget table, that shows me the weekly budget: Week - Budget - Article

Now I would like to get a running total for the MTD comparision. For this purpose I divide the weekly budget by the number of working days:

Budget per Day = DIVIDE([Budget], [# Working Days per Week])

Since I have a fiscal calender with specific end and start dates for each month, I can't use the time intelligence functions with DAX. Therefore I have developed this function:

Budget MTD = 
CALCULATE (
    SUMX (
        'Calendar',
        [Budget per Day]
    ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Fiscal Year]
            = VALUES ( 'Calendar'[Fiscal Year] )
            && 'Calendar'[Fiscal Month Number]
                = MAX ( 'Calendar'[Fiscal Month Number] )
            && 'Calendar'[Date]
                <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[IsWorkingDay] = 1
    )
)

Somehow, I always get the Budget for the whole month. What is missing here?

I think somehow I need to add something like < = TODAY, right?

This measure will be used in a matrix with a filter for the current month.

Solution

  • Since you are using a matrix with the month on the rows, the MAX( 'Calendar'[Date] ) will return the last day of the month in the current row. If Calendar[Date] is of type Datetime and in contains a real Date, it's possible to simply add the condition on TODAY() like follows

    Budget MTD =
CALCULATE(
    SUMX(
        'Calendar',
        [Budget per Day]
    ),
    FILTER(
        ALL( 'Calendar' ),
        'Calendar'[Fiscal Year]
            = VALUES( 'Calendar'[Fiscal Year] )
            && 'Calendar'[Fiscal Month Number]
                = MAX( 'Calendar'[Fiscal Month Number] )
            && 'Calendar'[Date]
                <= MAX( 'Calendar'[Date] )
            && 'Calendar'[Date]
                <= TODAY()
            && 'Calendar'[IsWorkingDay] = 1
    )
)

    But this will just change the MTD value for last month, the current one. If instead we want to filter each month to calculate the MTD up to the same day of the current month, then an option might be to add the 'Calendar'[Fiscal Month Day Number] column to the Calendar table with the number of the day since the beginning of the month (I'm assuming that it might differ from the day of the date)

    Budget MTD =
VAR fiscalYear =
    MAX( 'Calendar'[Fiscal Year] )
VAR fiscalMonthNumber =
    MAX( 'Calendar'[Fiscal Month Number] )
VAR currentFiscalMonthDayNumber =
    CALCULATE(
        MAX( 'Calendar'[Fiscal Month Day Number] ),
        'Calendar'[Date]
            = TODAY(),
        ALL( 'Calendar' )
    )
RETURN
    CALCULATE(
        SUMX(
            'Calendar',
            [Budget per Day]
        ),
        ALL( 'Calendar' ),
        'Calendar'[Fiscal Year] = fiscalYear,
        'Calendar'[Fiscal Month Number] = fiscalMonthNumber,
        'Calendar'[Fiscal Month Day Number] <= currentFiscalMonthDayNumber,
        'Calendar'[IsWorkingDay] = 1
    )