Need DAX expression for grand total for a budget within a FISCAL YEAR

[ROBSKI]

I am trying to build out an expression that simply lists the grand total of a budget forecast for a project so I can use it in a chart to show the MAX line for the budget forecast. The chart will show when the funding is needed each month with a line going up the the MAX line. This is simple in excel but with DAX and the way it works I cannot get it to display properly, I get close but then I get all the other dates in the fiscal year I do not want. So let me start by explaining what I have done

1. I created a rolling calendaer that goes out 5 years, in this calendar I have a fiscal year column that shows YYYYMM. My Fiscal year starts on Oct 1 and end Sept 30 so a date of 202301 would be fiscal year 2023 and month October. See below

2. I have a budget table that has records for all my projects, each project has the months (date column, always first of the month) and the amounts (Budget_Forecast) I need for every month of the project. An example might be

Oct 1, 2022, 11000

Nov 1, 2022, 15000

Dec 1, 2022, 12000

Jan 1, 2023, 15000

3. I built out a running total measure that I used in my pivot table to show the total budget increasing throught the project

=CALCULATE(

SUM(CR_Budget_Forecast[Budget_Forecast]),

FILTER(ALLSELECTED(calendar[Date]),

ISONORAFTER(calendar[Date],MAX(CR_Budget_Forecast[date]),DESC)))

4. Now I want to build a measure that only displays the total budget for each month of the fiscal year that the project is being worked. Here is my table and chart without the grand total

The Budget forecast column is correct, it is a running total of the monthly budget, the green line is the funds expended and the red is the funds provided.

When I try to build the measure for a grand total for budget forecast I do not get what I am looking for. What I want is a column that shows to $2,119,418.29 in every row from start to finish, this way I can ploy it as a MAX line.

Note I am doing this in Power Pivot and not Bi, I am aware there is a MAX line function in Bi but not all persons in the company have Bi but they do have excel. 

Also note this is dynamic and I need the total budget amount to change based on the project selected. Below is what BI does and what I want to replicate in Excel

Note the Budget line also extend out to the end of expenditures and does not stop when it meets budget forecast. I have tried several measures using calculate with different filters. I can get it to display using allselected but it also brings back all YYYYMM dates from the calendar and I only want the dates displayed from the first month of the budget to the last month of expenditures.

[tamerj1 · Accepted solution]

@ROBSKI 
Please refer to attached sample file with the solution

Total Budget = 
IF ( 
    NOT ISEMPTY ( CR_EXP_Table ),
    MAXX ( 
        ALLSELECTED ( 'calendar'[FY-YYYYMM] ),
        [Budget Forecast]
    )
)

[tamerj1 · Accepted solution]

@ROBSKI 
Please refer to attached sample file with the solution

Total Budget = 
IF ( 
    NOT ISEMPTY ( CR_EXP_Table ),
    MAXX ( 
        ALLSELECTED ( 'calendar'[FY-YYYYMM] ),
        [Budget Forecast]
    )
)

[ROBSKI]

That works a heck of allot better, here is the result when I build out the easure

As you can see one issue is it is not filling in the rows where the actual budget for the projet is expected to start. I figure this is because of this 

NOT ISEMPTY ( CR_EXP_Table )

as they line up, so I changed it to use the budget forecat table and it lines up better for what I need.

At least now I can plot th max budget line from the start of the project up to the expected end date and have the monthly budgets run up to it for presentation purposes. Actually this turns out to be better that a max line all accross all dates because it shows expenditures after the expected date for the project completion.

Thanks for you help, I appreciate it

[tamerj1 · Accepted solution]

HI @ROBSKI 
Simple amendment 

Total Budget = 
IF ( 
    OR ( 
        NOT ISEMPTY ( CR_EXP_Table ),
        NOT ISEMPTY ( CR_Budget_Forecast )
    ),
    MAXX ( 
        ALLSELECTED ( 'calendar'[FY-YYYYMM] ),
        [Budget Forecast]
    )
)

[ROBSKI]

That did it, works good as far as I could test it out.

[tamerj1]

Hi @ROBSKI 
Please try

Total Budget =
MAXX ( ALLSELECTED ( 'calendar'[FY-Year Month] ), [Budget Forecast] )

[ROBSKI]

Hey thanks for the suggestion, I ran it and it works but it also brings back all the years in the FY-YYYYMM similar to the image above except it has the grand total for all months. Now if I can filter out the months before the first month of the budget and after the last month of the expended I would have what I am looking for.

[tamerj1]

@ROBSKI 
How does your data model look like? Can you share a screenshot?

[ROBSKI]

Are you referring to the relationship model?

[tamerj1]

Hi @ROBSKI 

Please try

Total Budget =
MAXX (
    CALCULATETABLE (
        ALLSELECTED ( 'calendar'[FY-Year Month] ),
        CROSSFILTER ( 'calendar'[FY-YYYTMM], CR_EXP_Table[FY_Period], BOTH )
    ),
    [Budget Forecast]
)

[ROBSKI]

this is the response from the error check

CROSSFILTER function can only use the two column references participating in a relationship.

I also do not have this column in the calendar

'calendar'[FY-Year Month]

[tamerj1]

@ROBSKI 

What are the two columns between the two tables involved in the relationship?

[ROBSKI]

That would be the calendar[date] and the  CR_EXP_Table[Posting Date] but when I use them I get all he rows again just like above. What I am thinking to simplify this is the make a calculated column or another column in the main data table that has the grand budget for each project, then with a simple calculate(sum) I think I will get exactly what I am looking for and I will not have to run a iterator function with filters taking up processing power.

[tamerj1]

@ROBSKI 

A calculated column won't be dynamic. Please try

Total Budget =
MAXX (
CALCULATETABLE (
VALUES ( 'calendar'[Date] ),
CROSSFILTER ( 'calendar'[Date], CR_EXP_Table[Posting Date], BOTH ),
ALLSELECTED ()
),
[Budget Forecast]
)

[ROBSKI]

It does the same thing as the other measures, it works but it also brings up all the months in the calendar.

[ROBSKI]

Well I created a new budget column in the data table but the new SUM or Calculate mease does not work either, it adds up all the rows for the same month for each project, this throws the buget total way high. Back to the drawing board

[ROBSKI]

I tried this measure but it does the opposite, it calculated the grand total for the dates I do not want to include and then calculates the total for each specific month during the project.

=CALCULATE(SUM(CR_Budget_Forecast[Budget_Forecast]),DATESBETWEEN('calendar'[date],MIN(CR_Budget_Forecast[date]),MAX(CR_EXP_Table[Posting Date])))