Creating a monthly cost table based on start & end dates and average cost per month?

[SevsBo]

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.

[Anonymous · Accepted solution]

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.

[SevsBo]

Hi @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]

@SevsBo 

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.