Creating a matrix to show a value for months between a start and end date...

[Jamesp435]

Hi 

A newby here.  I want to create a matrix that will total the utilisation for a month between a start and end date.  If a start date is mid of end of a month, that will still be included in the month, and convesely if the end date is start or mid month it will still represent that month.

The problem I have:
- The difference between start and end dates can be up to 5 years.  Can I do this exercise without having to create a column for each month (eg. Jan22, Feb22, etc) as that's a lot of columns and calculations

- I have created a date table (with each day of the week, and another column to reprent the month).  I link the two tables by start date.  However, in the visual it seems to only pick up the Jan only. 

Below is the data:

This is the answer I would like to achieve:

	Jan	Feb	Mar
Jane	75	75	50
Fred		75	75
Iris	30	30

Any help on this gratefully received.

Many thansk 

James 

[DimaMD · Accepted solution]

Hi @Jamesp435  try it measure

the relationship between the fact table and the date table must be unlinked

Measure = 
SUMX( 
    FILTER (
        'table',
        'table'[start] <= MAX ( 'dates'[Date] )
&& 'table'[end] >= MIN ( 'dates'[Date] )
),[Utilisation]
)

[Jamesp435]

Nice one.  Thanks for this Dima.  Good luck being in Ukraine.