topic Re: Another DAX Calculation Total Not Working in DAX Commands and Tips

Another DAX Calculation Total Not Working

james8992 — Thu, 03 Oct 2019 17:58:19 GMT

I'm working with consulting projects and am adjusting the schedule if the 90 schedule is greater then the hours remaining in the project (Backlog Hours). In the example, the project is scheduled for 300 hours but there are only 145.75 remaining. I have a DAX formula to correctly apply this at the project level where there is FILTER context but have not been able to solve for the total since the total for 90 Days Schedule and 90 Day Schedule Adj should not be the exact same due to my formula reducing the schedule when greater than the remaining hours.

Hours Scheduled =

SUM('Consulting Forecast Hours Fact_V'[SumTotalHours])

90 Days Schedule =

SUMX (

VALUES ( Date_V[Date] ),

IF (

AND ( Date_V[Date] >= TODAY (), Date_V[Date] <= TODAY () + 90 ),

[Hours Scheduled],

BLANK ()

))

90 Day Schedule Adj =

SUMX(

SUMMARIZE(VALUES('SF Project_V'[Project Name]),

"BV", [Total Backlog Hours_R],

"90 Day Schedule1", [90 Days Schedule]),

MIN([BV], [90 Day Schedule1]))

Thanks in advance

Re: Another DAX Calculation Total Not Working

Anonymous — Fri, 04 Oct 2019 11:37:38 GMT

VAR tbl =

ADDCOLUMNS(
SUMMARIZE(
VALUES('SF Project_V'[Project Name]),

"BV", [Total Backlog Hours_R],

"90 Day Schedule1", [90 Days Schedule]
),

"MinVal",MIN([BV], [90 Day Schedule1])
)

RETURN

SUMX(

tbl;
[MinVal]
)

Re: Another DAX Calculation Total Not Working

james8992 — Fri, 04 Oct 2019 14:09:22 GMT

Kristjan - First THANK YOU so much for taking the time to reply.

When applying the formula you provided I'm getting an error saying that "The syntax for 'RETRUN" is incorrect." I'm trying to unwind the error but I'm not sure how to fix it.

Re: Another DAX Calculation Total Not Working

Anonymous — Fri, 04 Oct 2019 14:20:04 GMT

just change the seperator commonly used in Europa ";" to "," AND you have to keep the name of the function
90 Day Schedule Adj =
VAR tbl = ...

Re: Another DAX Calculation Total Not Working

james8992 — Fri, 04 Oct 2019 14:37:06 GMT

Still getting the same error.

Re: Another DAX Calculation Total Not Working

Anonymous — Fri, 04 Oct 2019 18:31:39 GMT

you are now missing VAR tbl =

90 Day .... =
VAR tbl =

AND it seems like you are also missing the ")" to close the ADDCOLUMS function, it should be above the RETURN

Regards,

Kristjan