Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there!
I have two unrelated tables:
1. Calendar table, single date
2. Project table, two dates: start date, end date.
I use a date slicer, and I want to count how many days the project period covers during that time frame.
I tried many measures, such as:
Days =
CALCULATE(
DISTINCTCOUNT('Calendar'[Date]),
'Job'[Start] <= MAX('Calendar'[Date]),
'Job'[End] >= MIN('Calendar'[Date])
)
In the example, it should be 4 (because 4 days in Feb 2020) but I only get 29, it counts the whole month. I keep searching everywhere for a solution but I can't find any....
Please help me! 🙂
Solved! Go to Solution.
Hi @Anonymous ,
How about this?
Days =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] >= MAX ( Job[Start] )
&& 'Calendar'[Date] <= MAX ( Job[End] )
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
How about this?
Days =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] >= MAX ( Job[Start] )
&& 'Calendar'[Date] <= MAX ( Job[End] )
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I fixed the days calculation like this:
I created two extra measures:
JobMin =
VAR JobMin = CALCULATE(
MINX('Job',[Start]),
'Job'[Start] <= MAX('Calendar'[Date]),
'Job'[End] >= MIN('Calendar'[Date])
)
RETURN
if(
NOT ISBLANK(JobMin) && JobMin <MIN('Calendar'[Date]),
MIN('Calendar'[Date]),
JobMin
)
and
JobMax =
VAR JobMax = CALCULATE(
MAXX('Job',[End]),
'Job'[Start] <= MAX('Calendar'[Date]),
'Job'[End] >= MIN('Calendar'[Date])
)
RETURN
if(
NOT ISBLANK(JobMax) && JobMax >MAX('Calendar'[Date]),
MAX('Calendar'[Date]),
JobMAX
)
And so I fixed the days calculation like this:
Days =
Var MinJob = [JobMin]
VAR MaxJob = [JobMax]
Var MaxDate = MAX('Calendar'[Date])
var MinDate = MIN('Calendar'[Date])
RETURN
CALCULATE(
SUMX('Job',DATEDIFF(if('Job'[Start]<MinJob,MinJob,'Job'[Start]), if('Job'[End]>MaxJob, MaxJob, 'Job'[End]), DAY) +1),
'Job'[Start] <= MaxDate,
'Job'[End] >= MinDate
)
Result:
(ps: current utilization = quantity x days)
Still checking all numbers...
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |