Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Jamesp435
Advocate I
Advocate I

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

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:

Jamesp435_0-1674558186337.png

 

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 

1 ACCEPTED SOLUTION
DimaMD
Solution Sage
Solution Sage

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]
)

 

Screenshot_36.jpg

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

2 REPLIES 2
DimaMD
Solution Sage
Solution Sage

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]
)

 

Screenshot_36.jpg

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.