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
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
Solved! Go to 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]
)
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]
)
Nice one. Thanks for this Dima. Good luck being in Ukraine.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |