Calculate daily cumulative cost for tasks with different start and end dates

[Anonymous]

Hi,

I currently have a table structure similar to this. I am interested in finding the cumulative daily cost from the start and end of the tasks. How could I go about doing this?

Please note, each task is independent of one another and can have overlapping days.

Task	Start Date	End Date	Total
1	1 Jan 2019	5 Jan 2019	200
2	2 Jan 2019	4 Jan 2019	500
3	10 Jan 2019	27 Jan 2019	1500
4	1 April 2019	7 April 2019	2000

The average cost may look something like this over time

1 Jan 2019 = 40

2 Jan 2019 = 80 + 166.7 = 246.7

...

5 Jan 2019 = 700

...

30 March 2019 = 2200

...

7 April 2019 = 4200

Thanks,

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can try to use following measure formula if it suitable for your requirement:

Measure =
VAR currDate =
    MAX ( Calendar[Date] )
VAR summary =
    SUMMARIZE (
        Tabel,
        [Task],
        [Start Date],
        [End date],
        "Daily", SUM ( Table[Total] ) / DATEDIFF ( [Start Date], [End date], DAY )
    )
RETURN
    SUMX (
        FILTER ( summary, [Start Date] <= currDate && [End date] >= currDate ),
        [Daily]
    )

Notice: I create a variable to store summary table with each task and its daily cost, the use iteration function with content of summary correspond records.

Regards,

Xiaoxin Sheng