IF ELSE Time Calculated Measure

[cruzp]

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 months and retain the base actuals on prior months to current for specific employee EMP01?

Measure = 
IF (     ISINSCOPE(DCC[CC_NAME])&&NOT(ISINSCOPE(DE[NameID])),
        SUMX(DE, [Hours]),
        IF (
            ISINSCOPE(DE[NameID]),
            [Hours],
            SUMX(DE, [Hours])
        )
    )

For this example, numbers from September 2024 to future months will be divided by 2. 

Any months prior to Sep 2024 will be retained.

[v-yilong-msft]

Hi @cruzp ,

I also create a table and show the matrix as you mentioned.

Next I create a measure and here is the DAX code.

Measure = 
VAR _CurrentMonth =
    MONTH ( TODAY () )
VAR _CurrentYear =
    YEAR ( TODAY () )
VAR _CurrentDate =
    TODAY ()
VAR _EmployeeID =
    SELECTEDVALUE ( 'Table'[Name] )
RETURN
    SUMX (
        FILTER ( 'Table', 'Table'[Name] = _EmployeeID ),
        IF (
            'Table'[Date] >= DATE ( _CurrentYear, _CurrentMonth, 1 ),
            'Table'[Total] / 2,
            'Table'[Total]
        )
    )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

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

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]

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]

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