topic Re: DAX - Monthly income tabulator - between start and end dates in DAX Commands and Tips

DAX - Monthly income tabulator - between start and end dates

T_J — Mon, 04 Aug 2025 10:39:07 GMT

Hi all,

Sorry, far from a DAX maestro and have been fiddling with Sum, filter and calculation to make this work but it's still not coming out as I'd expect.

Situation is I have two tables, one which has a list of all upcoming projects ('Project List', including a monthly income and start and end dates of the project. Second table is a Calendar table spanning 2025-2035. What I am looking to do is for every 1st of the month on the calendar table, for it to sum up the value of all monthly income expected in based on whether the 1st of the month is between the start and end dates of the project.

I've started trying to make it with a measure or just calculating it within the column, closest I've gotten is;

Monthly Income = CALCULATE(Sum('Project List'[Monthly Income]),FILTER('Project List','Calendar'[Date]>='Project List'[Start Date] && 'Calendar'[Date] < 'Project List'[End Date]))

Which shows the right income value but only appears once on the list (I had expected it to show the same value against every date on the calendar column as I hadn't set any filter specifying only sum up the figures on the 1st of every month, which is another issue I need to overcome...)

Thank you in advance for anyone that can help!

Re: DAX - Monthly income tabulator - between start and end dates

MFelix — Mon, 04 Aug 2025 11:00:17 GMT

Hi T_J ,

Based on your explanation I assume that your calendar table has a relationship with the Project list table, based on the start date probably.

Try to change your calculation to something similar to this:

Monthly Income = CALCULATE(Sum('Project List'[Monthly Income]),FILTER('Project List','Calendar'[Date]>='Project List'[Start Date] && 'Calendar'[Date] < 'Project List'[End Date]), CROSSFILTER('Calendar'[Date],'Project List'[Start Date], None ))

The additional CROSSFILTER syntax will remove the relationship from this calculation and then use the filters of the dates to return values.

Re: DAX - Monthly income tabulator - between start and end dates

rohit1991 — Mon, 04 Aug 2025 11:26:54 GMT

Hi T_J
Here's a step-by-step solution using your two tables:

'Project List' (with Start Date, End Date, Monthly Income)
'Calendar' (with daily dates from 2025 to 2035)

Your goal is to show monthly income only on the 1st of each month, where that date falls within a project's duration. Here's how you can do that:

Go to the Calendar table and create this measure:

MonthlyIncomeOn1st = VAR CurrentDate = MAX('Calendar'[Date]) RETURN IF ( DAY(CurrentDate) = 1, CALCULATE ( SUM ( 'Project List'[Monthly Income] ), FILTER ( 'Project List', 'Project List'[Start Date] <= CurrentDate && 'Project List'[End Date] >= CurrentDate ) ) )

Add to a Visual

Insert a Table visual
Drag Calendar[Date] and the new measure MonthlyIncomeOn1st into the visual

Re: DAX - Monthly income tabulator - between start and end dates

T_J — Mon, 04 Aug 2025 13:34:05 GMT

Thank you! This worked perfectly! Just needed to apply the one other filter to the date to keep it to the 1st only and that's all worked out!

Re: DAX - Monthly income tabulator - between start and end dates

T_J — Mon, 04 Aug 2025 13:35:03 GMT

Thank you! The other poster who posted first had a solution that worked in the table format I was looking for but this might be worth it for any others looking

Re: DAX - Monthly income tabulator - between start and end dates

v-lgarikapat — Tue, 05 Aug 2025 05:08:10 GMT

Hi T_J

Thanks for reaching out to the Microsoft fabric community forum.

rohit1991 ,

MFelix ,

Thank you for your prompt response it's truly appreciated.

Best Regards,

Lakshmi