Spread of total value across days

[coding7]

Hi, 

I've this dax measure:

Reserved Instances Daily Amortized Cost= 
VAR ContextDate = MAX ( 'Date'[Date] )
VAR CostStartDate = CALCULATE ( MIN ( 'Fact Partner Center Costs'[Charge Start] ), ALL ( 'Date' ) )
VAR CostEndDate = CALCULATE ( MAX ( 'Fact Partner Center Costs'[Charge End Date] ), ALL ( 'Date' ) )
VAR DailyAmortizedCost = MIN ('Fact Partner Center Costs'[Daily Amortized Cost])
VAR ReservedInstancesCost =
    CALCULATE (
        SUMX ( VALUES ( 'Date'[Day] ), DailyAmortizedCost ),
        ALL ( 'Date')
    )

RETURN
IF (
    ContextDate >= CostStartDate && ContextDate <= CostEndDate,
    ReservedInstancesCost,
    BLANK ()
)

which shows me the correct total for the specific month. The problem is the value needs to be divided by the number of days in the month and distributed to the remaining days. The sum of the days will be the total value.

So for this example:

the value will be 4.54 (140.64 / 31(number of days for July)) and the desired output will be:

Year	Month	Day	Reserved Instances Daily Amortized Cost
2023	July	1	4,54
2023	July	2	4,54
2023	July	3	4,54
2023	July	4	4,54
2023	July	5	4,54
2023	July	6	4,54
2023	July	7	4,54
2023	July	8	4,54
2023	July	9	4,54
2023	July	10	4,54
2023	July	11	4,54
2023	July	12	4,54
2023	July	13	4,54
2023	July	14	4,54
2023	July	15	4,54
2023	July	16	4,54
2023	July	17	4,54
2023	July	18	4,54
2023	July	19	4,54
2023	July	20	4,54
2023	July	21	4,54
2023	July	22	4,54
2023	July	23	4,54
2023	July	24	4,54
2023	July	25	4,54
2023	July	26	4,54
2023	July	27	4,54
2023	July	28	4,54
2023	July	29	4,54
2023	July	30	4,54
2023	July	31	4,54

Additionally I have a slicer with the contract key, and if I selected more than one it has to do the sum per day, that is, for contract_key = 10 the value is 4.54 and for contract_key = 20 the value is 4 , if I selected both at the same time, the value of the measure must be 8.54 and then do the respective sum of the total days.

ps: I showed only for July 2023 but the scenario is for more than one month and year

Thanks you !

[Anonymous · Accepted solution]

Hi @coding7 ,

Here I create a sample to have a test.

Reserved Instances Daily Amortized Cost = 
VAR _ADD =
    ADDCOLUMNS (
        'Calendar',
        "Value",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Reserved Instances Cost] ),
                    FILTER (
                        'Table',
                        'Table'[Year] = EARLIER ( 'Calendar'[Year] )
                            && 'Table'[Month] = EARLIER ( 'Calendar'[Month] )
                    )
                ),
                MAXX (
                    ALLEXCEPT ( 'Calendar', 'Calendar'[Year], 'Calendar'[Month] ),
                    'Calendar'[Day]
                )
            )
    )
RETURN
    SUMX ( _ADD, [Value] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.