Creating a Calculated Date Table and Sum Over Date Range

[MBenson415]

Hello,

I have a task list whose rows contain [Start Date] and [End Date], and [Cost].

I am trying to generate a calculated Calendar table that will display the Cost spread over the length of the dates ([Cost]/[End Date]-[Start Date]).

I was able to generate it using a CALCULATE and SUM calculated column with a Calendar table, with filters applied to the calculation that reference the [Start Date] and [End Date]. This seems to work OK, but since I've had to create a new table to do this, I lose the relationship to the primary data table, so I'm not able to filter and slice it.

Is there a more intelligent way to generate this data, again pivoted off of date (granular to the day level), that will allow me to slice and filter the data, and take advantage of existing relationships? There must be a way.

Current formula in date table:

Spend by Day_Current =
CALCULATE(
    SUM('Project Portfolio'[CostPerDay_Current]),
    FILTER('Project Portfolio',[Start Current_RF].[Date]<='Date'[Date].[Date]),
    FILTER('Project Portfolio',[End Current_RF].[Date]>='Date'[Date].[Date]))

Thanks!

[Anonymous]

Hi @MBenson415,

You can refer to below link to get the daily total cost based on records table and calendar table.

Records table:

Calendar: calculate table

Calendar = CALENDAR(FIRSTDATE('Records'[StartDate]),LASTDATE('Records'[EndDate])) 

Measure: calculate total cost by current calendar date.

Total Cost = SUMX(FILTER(ALL(Records),MAX('Calendar'[Date]) in CALENDAR([StartDate],[EndDate])),[Cost])

Create visual: calendar date as axis, measure as value.

Notice:

1. Calendar table is generated by records table, they not contains relationship.

2. Measure will filter virtual 'calendar' table which created by calendar function (parameters 'startdate' and 'enddate' are from current row content) by current calendar date and get the summary cost value.

Regards,

Xiaoxin Sheng

[MBenson415]

Hello Xiaoxin,

Thank you for the response! I am giddy seeing your reply, I think you're a bit of a legend around these parts 😉

Unfortunately, your solution was the same one that I came up with and talked about in my OP.

Let me clarify that I am looking for a way to maintain the relationship to the primary table, so that I may view those relationships as I please.

For example, the table contains [Program], ten different values; if I wanted to view a stacked chart of spend separated by program, how could I do that? There are other columns such as [Cost Center] which I may also want to filter by. The trouble is, by auto-generating a calendar table, the relationships are lost. SURELY there must be a solution to modeling this time-based function without losing relationship?

[Anonymous · Accepted solution]

Hi @MBenson415,

For this situation, records table contains the date range based on start date and end date, it is hard to create relationship between these columns.(your calendar date need to mapping to each day of this virtual range not only start date and end date)

In my opinion, I'd like to recommend your to expand these date range and add column to store detail range date, then use detail date to link calendar table.

Steps:

1. Enter to query editor add custom column.

2.  Expand list.

3. Create relationship between calendar date and records table.

4. Direct use related columns to create visuals.(you not need to write measures)

Notice: since the method will expand all the dates, it will generate huge amount of redundant records, it will effect the performance.(on my side original table: 100 rows; after expand: 5096 rows)

You can choose one of them as the solution.

Regards,

Xiaoxin Sheng

[MBenson415]

Xiaoxin,

It worked!!!

Thank you so much for the help. Brilliantly done.

-Marshall

[Ashish_Mathur]

Hi,

Show some data and the expected result.