Forum Discussion
Creating a Calculated Date Table and Sum Over Date Range
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
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
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?
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
Xiaoxin,
It worked!!!
Thank you so much for the help. Brilliantly done.
-Marshall