Forum Discussion

james8992's avatar
james8992
Frequent Visitor
6 years ago
Solved

Another DAX Calculation Total Not Working

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
  • Anonymous's avatar
    Anonymous
    6 years ago

    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]
    )

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]
    )
    • james8992's avatar
      james8992
      Frequent Visitor
      6 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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 = ...