Time erosion calculation

[mgrayTCB]

I have a model with two fact tables one that includes budgeted payments by date and another one that includes the current projeciton of payments by date. I am trying to figure out how to calcualte time slippage of each payment in days or months.

For example from the budgeted date of 1/14/2023 it is now +60 days (and also reduced in amount).
Any help would be appreciated

[v-janeyg-msft · Accepted solution]

Hi, @mgrayTCB 

According to your description, I create two measures to display your desired results.

Like this:

Reduction = 
VAR a =
    MAXX (
        FILTER ( ALL ( 'Table' ), [Date] < SELECTEDVALUE ( 'Table'[Date] ) ),
        [Date]
    )
VAR b =
    MAXX ( FILTER ( ALL ( 'Table' ), [Date] = a ), [BudgetNetFree] )
RETURN
    IF (
        [days of delay] <> "n/a",
        IF (
            [days of delay] = "0",
            SELECTEDVALUE ( 'Table'[BudgetNetFree] )
                - SELECTEDVALUE ( 'Table'[CurrentNetFee] ),
            b - SELECTEDVALUE ( 'Table'[CurrentNetFee] )
        )
    )

Reduction = 
VAR a =
    MAXX (
        FILTER ( ALL ( 'Table' ), [Date] < SELECTEDVALUE ( 'Table'[Date] ) ),
        [Date]
    )
VAR b =
    MAXX ( FILTER ( ALL ( 'Table' ), [Date] = a ), [BudgetNetFree] )
RETURN
    IF (
        [days of delay] <> "n/a",
        IF (
            [days of delay] = "0",
            SELECTEDVALUE ( 'Table'[BudgetNetFree] )
                - SELECTEDVALUE ( 'Table'[CurrentNetFee] ),
            b - SELECTEDVALUE ( 'Table'[CurrentNetFee] )
        )
    )

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[mgrayTCB]

Thank you for your help with this. I think your solution is right but I am not sure my question was clear enough. I am going to repost with a sample model and a broader question.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[mgrayTCB]

I cant easily sanitize the data and send the Pbx file but maybe further description of what I am looking for might help

BudgeteNetFee and CurrentNetFee are in separate fact tables. Budgeted was a snapshot of the Current table at the begining of the year. I am trying to calculate some measure of both time delay and dollar change. The two tables are related by a date table and a project table.

The result I am looking for is something like this... for a particular project we had 311k budgeted for 2/14/23 it is now expected to be 4/15/23 and has been reduced by 51k

Maybe it is not even possible with the data model I have but I was hoping to get some guidance.