Calculating Remaining Budget for rest of year?

[Anonymous]

So in my fact table I have one value column. There is another column that labels if this value is an Actual Amount or a Budget Amount. I also have a calendar table that is created from 1/1/18 - Today's date.

In a matrix where I can drill up and down by year, quarter, month, I want to have the following columns:

YTD Actuals 

YTD Budget

YTD Variance

YTD Budget Remaining

I have the first three columns down. My YTD Actuals measure is as such:

YTD Actuals = IF (MAX( 'Calendar'[Date] ) <= TODAY (), TOTALYTD([Actuals Measure],'Calendar'[Date]), BLANK () )

Where the measure "Actual Measure" is just a sum of the value column filtered for "Actuals". I need to add the if statement or else my matrix will show a blank column because there is data that is past today's date (for the budget). 

I have a similar measure for Budget and then another measure taking the variance between the two.

I am stumped on how to get the YTD Budget Remaining.  So for January I would want the measure to take the sum of the budget for February - December.  And for February I would want the measure to take the sum of the budget for March - December. I would then add this amount to the YTD Actuals measure to give a picture of how much of the budget is remaining.

Do I have to rework my current structure of the data or is there a way with a formula to do above?

Thanks.

[v-xjiin-msft]

@Anonymous

=> So for January I would want the measure to take the sum of the budget for February - December. 

In your scenario, how did you define this Janyary? Based on month of Today()? 

If so, please refer to following sample. See if it works for you:

Budget Remaining =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Amount Type] = "Budget"
            && MONTH ( 'Table'[Date] ) > MONTH ( TODAY () )
    )
)

If above doesn't satisfy your requirement. Could you please share us some sample data (which we can copy and paste directly) and its corresponding desired result if possible? So that we can make some tests rather than just guessing.

Thanks,
Xi Jin.