Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
So I have the below formula that works correctly. However, if I don't have a filter in place for the Column "Bucket", the formula returns the values for the Last Bucket Name in alphabetical order. This makes sense as my currProject variable is using LASTNONBLANK. I'm stuck on how to alter this so if I'm not filtered on bucket, it calculates for the table as a whole.
Even as a whole, I would still need the formula to calculate on a per Bucket basis as each bucket has a different _TotalProjectHours and a different Capacity.
Measure =
VAR __Date = MAX('Calendar'[Date])
VAR __StartDate = MINX('Master Data','Master Data'[Artificial Date])
VAR __FinishDate = MAXX('Master Data','Master Data'[Artificial Date])
VAR currProject = LASTNONBLANK('Master Data'[Bucket],[Bucket])
VAR __TotalProjectHours = SUMX(FILTER(ALL('Master Data'),'Master Data'[Bucket]=currProject),'Master Data'[# of cycles to fill order])
VAR __IdealHoursPerDay =
MAXX(FILTER(ALL('Master Data'),'Master Data'[Bucket]=currProject),'Master Data'[Capacity])
VAR __IdealConsumedHours =
__IdealHoursPerDay * (DATEDIFF(__StartDate,__Date,DAY) + 1)
RETURN
IF(
__Date < __StartDate - 1 || __Date > __FinishDate,
BLANK(),
__TotalProjectHours - __IdealConsumedHours
)
Solved! Go to Solution.
@PowerUser123 , Try like
Measure =
VAR __Date = MAX('Calendar'[Date])
VAR __StartDate = MINX('Master Data','Master Data'[Artificial Date])
VAR __FinishDate = MAXX('Master Data','Master Data'[Artificial Date])
VAR currProject = LASTNONBLANK('Master Data'[Bucket],[Bucket])
VAR __TotalProjectHours = SUMX(FILTER(ALL('Master Data'),if(isfiltered('Master Data'[Bucket]),'Master Data'[Bucket]=currProject, true())),'Master Data'[# of cycles to fill order])
VAR __IdealHoursPerDay =
MAXX(FILTER(ALL('Master Data'),if(isfiltered('Master Data'[Bucket]),'Master Data'[Bucket]=currProject, true())),'Master Data'[Capacity])
VAR __IdealConsumedHours =
__IdealHoursPerDay * (DATEDIFF(__StartDate,__Date,DAY) + 1)
RETURN
IF(
__Date < __StartDate - 1 || __Date > __FinishDate,
BLANK(),
__TotalProjectHours - __IdealConsumedHours
)
@PowerUser123 , Try like
Measure =
VAR __Date = MAX('Calendar'[Date])
VAR __StartDate = MINX('Master Data','Master Data'[Artificial Date])
VAR __FinishDate = MAXX('Master Data','Master Data'[Artificial Date])
VAR currProject = LASTNONBLANK('Master Data'[Bucket],[Bucket])
VAR __TotalProjectHours = SUMX(FILTER(ALL('Master Data'),if(isfiltered('Master Data'[Bucket]),'Master Data'[Bucket]=currProject, true())),'Master Data'[# of cycles to fill order])
VAR __IdealHoursPerDay =
MAXX(FILTER(ALL('Master Data'),if(isfiltered('Master Data'[Bucket]),'Master Data'[Bucket]=currProject, true())),'Master Data'[Capacity])
VAR __IdealConsumedHours =
__IdealHoursPerDay * (DATEDIFF(__StartDate,__Date,DAY) + 1)
RETURN
IF(
__Date < __StartDate - 1 || __Date > __FinishDate,
BLANK(),
__TotalProjectHours - __IdealConsumedHours
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |