Forum Discussion

cruzp's avatar
cruzp
Helper V
1 year ago

IF ELSE Time Calculated Measure

Hello, i have a calculated measure below in this table.   Is there a way to update the measure below that uses DAX like VAR that cuts by half the actuals on the current month we are in to future mo...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

cruzp , Try using below measure

 

Measure =
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR TargetEmployee = "EMP01"
RETURN
IF (
ISINSCOPE(DCC[CC_NAME]) && NOT(ISINSCOPE(DE[NameID])),
SUMX(
DE,
IF (
DE[NameID] = TargetEmployee &&
(YEAR(DE[Date]) > CurrentYear OR (YEAR(DE[Date]) = CurrentYear && MONTH(DE[Date]) >= CurrentMonth)),
[Hours] / 2,
[Hours]
)
),
IF (
ISINSCOPE(DE[NameID]),
IF (
DE[NameID] = TargetEmployee &&
(YEAR(DE[Date]) > CurrentYear OR (YEAR(DE[Date]) = CurrentYear && MONTH(DE[Date]) >= CurrentMonth)),
[Hours] / 2,
[Hours]
),
SUMX(
DE,
IF (
DE[NameID] = TargetEmployee &&
(YEAR(DE[Date]) > CurrentYear OR (YEAR(DE[Date]) = CurrentYear && MONTH(DE[Date]) >= CurrentMonth)),
[Hours] / 2,
[Hours]
)
)
)
)

  • cruzp's avatar
    cruzp
    Helper V
    1 year ago

    hi bhanu_gautam the date is in different table named DD, but i could not referenced it? how do i able to call it?

    • bhanu_gautam's avatar
      bhanu_gautam
      Super User
      1 year ago

      To reference a date from a different table in DAX, you need to ensure that there is a relationship between the tables. Assuming you have a relationship between the DE table and the DD table, you can use the RELATED function to access the date from the DD table. Here is the updated measure:

       

      Measure =
      VAR CurrentMonth = MONTH(TODAY())
      VAR CurrentYear = YEAR(TODAY())
      VAR TargetEmployee = "EMP01"
      VAR IsCurrentOrFutureMonth =
      YEAR(RELATED(DD[Date])) > CurrentYear ||
      (YEAR(RELATED(DD[Date])) = CurrentYear && MONTH(RELATED(DD[Date])) >= CurrentMonth)

      RETURN
      IF (
      ISINSCOPE(DCC[CC_NAME]) && NOT(ISINSCOPE(DE[NameID])),
      SUMX(
      DE,
      IF (
      DE[NameID] = TargetEmployee && IsCurrentOrFutureMonth,
      [Hours] / 2,
      [Hours]
      )
      ),
      IF (
      ISINSCOPE(DE[NameID]),
      IF (
      DE[NameID] = TargetEmployee && IsCurrentOrFutureMonth,
      [Hours] / 2,
      [Hours]
      ),
      SUMX(
      DE,
      IF (
      DE[NameID] = TargetEmployee && IsCurrentOrFutureMonth,
      [Hours] / 2,
      [Hours]
      )
      )
      )
      )

      • cruzp's avatar
        cruzp
        Helper V
        1 year ago

        hi bhanu_gautam they dont have relationship. how can i make it work if that's the case?