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 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.
Need Help

5 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.