Forum Discussion
Responsive ResidentCreating a monthly cost table based on start & end dates and average cost per month?
I have a table with the following structure:
| Cost | StartDate | EndDate | Total | Other irrelevant columns |
| Item1 | 2025/01/01 | 2025/12/31 | 12000 | x |
| Item2 | 2025/04/01 | 2026/03/01 | 6000 | x |
| Item3 | 2025/06/01 | 2026/05/01 | 18000 | x |
| Item4 | 2025/07/01 | 2026/06/01 | 24000 | x |
The desired outcome is to be able to calculate how much spend we will incur in every month of the year. In a table format it would look something like this:
| Cost | Jan 2025 | Feb 2025 | Mar 2025 | Apr 2025 | May 2025 | Jun 2025 | Jul 2025 | Aug 2025 | continued |
| Item1 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | ... |
| Item2 | 0 | 0 | 0 | 500 | 500 | 500 | 500 | 500 | ... |
| Item3 | 0 | 0 | 0 | 0 | 0 | 1500 | 1500 | 1500 | ... |
| Item4 | 0 | 0 | 0 | 0 | 0 | 0 | 2000 | 2000 | ... |
I have managed to get a version of this working by creating:
- a DateTable table with MonthStart column, showing start day of every month,
- an MonthlyAverageCost column in the original table,
- a measure:
CALCULATE(
SUM('Table'[MonthlyAverageCost]),
FILTER('Table', 'Table'[EndDate] >= MIN('DateTable'[MonthStart] && 'Table', 'Table'[StartDate] <= MAX('DateTable'[MonthStart])
))
The results are correct but it's all done within DAX rather than a new table, so I am wondering if there is a better way to do this?
PS: if anyone can tell me how to properly format tables that would be great, as the end result is nothing like the preview and I get an error about incorrect formatting despite using the built-in table maker.
Thank you bhanu_gautam
Hi, SevsBo
Based on your description, I've created the following sample data:
There is no relationship between the two tables:
I've established the following two measures:
Measure 2 = VAR _a = CALCULATE(SUM('Table'[MonthlyAverageCost]),'Date'[Date]>=SELECTEDVALUE('Table'[StartDate])&&'Date'[Date]<=SELECTEDVALUE('Table'[EndDate])) RETURN IF(ISINSCOPE('Date'[YearMonth]), IF(MIN('Date'[Date])>=SELECTEDVALUE('Table'[StartDate])&&MIN('Date'[Date])<=SELECTEDVALUE('Table'[EndDate]),_a,0),_a)Measure 4 = SUMX(VALUES('Table'[Cost]),[Measure 2])Finally, use the matrix visual to set it up as follows:
Here are the results:
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Thank you bhanu_gautam
Hi, SevsBo
Based on your description, I've created the following sample data:
There is no relationship between the two tables:
I've established the following two measures:
Measure 2 = VAR _a = CALCULATE(SUM('Table'[MonthlyAverageCost]),'Date'[Date]>=SELECTEDVALUE('Table'[StartDate])&&'Date'[Date]<=SELECTEDVALUE('Table'[EndDate])) RETURN IF(ISINSCOPE('Date'[YearMonth]), IF(MIN('Date'[Date])>=SELECTEDVALUE('Table'[StartDate])&&MIN('Date'[Date])<=SELECTEDVALUE('Table'[EndDate]),_a,0),_a)Measure 4 = SUMX(VALUES('Table'[Cost]),[Measure 2])Finally, use the matrix visual to set it up as follows:
Here are the results:
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SevsBoHi Anonymous,
Thanks for that, it works! Only caveat is I noticed I have to explicitly mark the Year-Month column as Date and then I get the results that match.
One question I would have, to continue with this piece of work, is that I now have to roll this data up to create monthly and YTD comparisons between costs and expected costs.
Say, total costs for June in your scenario show 3000 costs and for July 5000. I would need to calculate what that means for a total monthly and YTD cost when looking at this data in June or July and compare that with what we expected to see to determine KPIs.
Let's say total cost as seen in June, (based on the data you provided) would be 3000 in month and 9000 YTD. In July it would be 5000 in-month and 14000 YTD. Then to compare that against our expected costs for those months.
Would I be better off creating a calculated table of Dates & Values per Cost rather than to do it with Measures?
- bhanu_gautam
Super User
Create a Date Table that includes all the months you need:
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2025, 1, 1), DATE(2026, 12, 31)),
"MonthStart", EOMONTH([Date], -1) + 1
)Add a calculated column to your original table to calculate the monthly average cost:
MonthlyAverageCost =
DIVIDE (
'Table'[Total],
DATEDIFF('Table'[StartDate], 'Table'[EndDate], MONTH)
)Add a calculated column to determine if a given month falls within the start and end dates:
IsInMonth =
IF (
'DateTable'[MonthStart] >= 'Table'[StartDate] &&
'DateTable'[MonthStart] <= 'Table'[EndDate],
1,
0
)Create a measure to calculate the total monthly cost:
dax
TotalMonthlyCost =
CALCULATE (
SUM('Table'[MonthlyAverageCost]),
FILTER (
'Table',
'Table'[IsInMonth] = 1
)
)Add a matrix visualization to your report.
Add the Cost column from your original table to the rows of the matrix.
Add the MonthStart column from the Date Table to the columns of the matrix.
Add the TotalMonthlyCost measure to the values of the matrix.- SevsBo
Thanks, that's quite similar to the approach I'm using at the moment!
Just a few questions, for the last column & measure, am I adding them to the DateTable or original table?
I'm assuming a relationship has to exists between the two regardless?EDIT: After trying to implement the step "Add a calculated column to determine if a given month falls within the start and end dates:" I am noticing it is not allowing me to select the StartDate or EndDate columns from the original table. If I try to build it in the original table it does not recognize the DateTable.
