DAX - Monthly income tabulator - between start and end dates

[T_J]

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!

[v-lgarikapat]

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

[rohit1991]

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

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

[T_J]

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

[MFelix · Accepted solution]

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.

[T_J]

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!