Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi.
I have been trying without success to calculate the number of days within a given period of time that streches between different months. I need to display in a visual the number of days X month.
The dataset I have is something like this:
As you can see, some periods begin in one month and end in the following.
The result I am looking for is as below, where the number of days used is placed in the respective month it belongs to:
Any help is appreciated! I am stuck with this issue for days...
Thanks!!
Solved! Go to Solution.
Hi lltat.
Look this video below, maybe helps you.
https://www.youtube.com/watch?v=R8tDKwimSsY
Best regards.
Hi @Anonymous,
As mentioned in the Video suggested, you can use a dsiconnected date table to generate the values you are looking for along with the following Measure
Days of Use =
var _monthstart = MIN('Calendar'[Date])
var _monthend = MAX('Calendar'[Date])
return
SUMX('Table',
SWITCH(TRUE(),
'Table'[Start Time] < _monthstart && 'Table'[End Time] < _monthstart , BLANK(), // Blank for empty intersections
'Table'[End Time] < _monthend && 'Table'[Start Time] >= _monthstart, DATEDIFF('Table'[Start Time], 'Table'[End Time], DAY),
'Table'[Start Time] >= _monthstart && 'Table'[End Time] >= _monthend && DATEDIFF('Table'[Start Time], _monthend, DAY) > 0, DATEDIFF('Table'[Start Time], _monthend, DAY),
'Table'[Start Time] < _monthstart && 'Table'[End Time] <= _monthend, DATEDIFF(_monthstart, 'Table'[End Time], DAY)+1,
BLANK())
)
Results in the following Matrices
I am also attaching a sample pbix with a simple calendar table and the measure.
I hope this helps.
Proud to be a Super User!
good...
the truth is that the formula gives me results much closer to those of the video, but I have 2 drawbacks. when the stay for example is an annual stay, it adds the 365 days of the year in the subtotals but in the table or graphs it only shows me the month of beginning and closing.
and on the other hand randomly there are rows that do not count them
Hi lltat.
Look this video below, maybe helps you.
https://www.youtube.com/watch?v=R8tDKwimSsY
Best regards.
Thanks!! That was exactly what I was looking for! (★‿★)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |