DAX - Running Total of Revenue Goal Shortfall/Overage

[Fishy]

Hello,

PBI novice here looking for some help with a form of a DAX Running Totals calculation.

Background: I'm working with monthly revenue goals for my company's various markets and daily revenue values reported through our internal BI cube. I've divided these monthly revenue goals across all days in the month to match the granularity or the revenue data. I have two dimension tables - Market & Calendar. I have one fact table - Revenue - which has a date key, market key, a column for my revenue category (internal classification of revenue such as revenue, revenue pending payment from customers, sales pipeline, etc.), and a column with the actual revenue values in USD.

My Goal: I would like to create a running total of the revenue:goal shortfall/surplus to figure out how much revenue must be generated in the rest of the year to hit our yearly goal. Ideally this goal shortfall/surplus would be added to the year's remaining revenue goals. Please see the attached picture for my ideal.

Any idea how I can calculate the distributed remainder (New Monthly Goal) using DAX?

Please let me know what additional details may be needed to solve this.

Thank You

Edit for any future viewers: A similar answer to this topic can be found here

[v-lid-msft · Accepted solution]

Hi @Fishy ,

As the screenshot you shared, We can create those simple measure to meet your need, If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Revenue Goal =
IF ( ISINSCOPE ( Revenue[date key].[Month] ), 100, 1200 )

Difference =
[Revenue Goal] - SUM ( Revenue[revenue value] )

New Monthly Goal =
VAR selectmonth =
    SELECTEDVALUE ( Revenue[date key].[MonthNo] )
VAR thismonth =
    MONTH ( TODAY () )
VAR allDifference =
    CALCULATE ( [Difference], ALL ( Revenue ) )
VAR leftMonth = 12 - thismonth
VAR goalmonth =
    INT ( allDifference / leftMonth )
VAR goalthis = allDifference - goalmonth * leftMonth
RETURN
    IF (
        ISINSCOPE ( Revenue[date key].[Month] ),
        SWITCH (
            TRUE (),
            selectmonth > thismonth, goalmonth,
            selectmonth = thismonth, goalthis,
            "-"
        ),
        allDifference
    )

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[Fishy]

Awsome, that worked as desired. Thank You!