Forum Discussion

kathod's avatar
kathod
Regular Visitor
7 years ago

Distributing multiple values over different time periods

Hello, I am working with contracts that have a total value and an end date. I want to spread the total value on each working day between today and the end date. So far I calculated the daily value ...
Need Help
Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

How is the first date determined? Is it just TODAY()?  It should be easier to get your result by just counting the working days between now and the end date and using that as a divisor.

 

Assuming you put this measure in a table split up by Contract No for context, something like this should work for both individual rows and the total. My apologies for not using the table/column names you provided, but my German is very poor.

Daily Amt =
SUMX (
    SUMMARIZE (
        Table1,
        Table1[Contract No],
        "Daily Total", DIVIDE (
            SUM ( [Total Value] ),
            CALCULATE (
                COUNTROWS ( 'Calendar' ),
                DATESBETWEEN ( 'Calendar'[Date], TODAY (), LASTDATE ( Table1[End Date] ) ),
                'Calendar'[IsWorkingDay] = 1
            )
        )
    ),
    [Daily Total]
)