Forum Discussion

arunh100's avatar
arunh100
Frequent Visitor
1 year ago
Solved

Create simple DAX Measure

Hi Team   Given the below Input in Exceptions Table: CreatedMthYr Status Count of Task ID 202410 Open 9 202410 OnHold 10 202410 Completed 20 202411 Open 20 202411 OnHo...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi arunh100 

     

    Please try the following possible solution:

    YTDMeasure = 
    VAR MaxMonthYear = CALCULATE(MAXX(ALL('Exceptions'), 'Exceptions'[CreatedMthYr]))
    VAR CurrentMonthYear = SELECTEDVALUE('Exceptions'[CreatedMthYr])
    VAR CurrentMonth = MOD(VALUE(CurrentMonthYear), 100)
    VAR CurrentYear = INT(DIVIDE(VALUE(CurrentMonthYear), 100, 0))
    VAR PreviousMonthYear = IF(
        CurrentMonth = 1,
        (CurrentYear - 1) * 100 + 12,
        CurrentYear * 100 + (CurrentMonth - 1)
    )
    VAR CurrentMonthExceptions = CALCULATE(
        COUNT(Exceptions[Task ID]),
        'Exceptions'[CreatedMthYr] = CurrentMonthYear
    )
    VAR PreviousMonthExceptions = CALCULATE(
        COUNT(Exceptions[Task ID]),
        'Exceptions'[CreatedMthYr] = PreviousMonthYear
    )
    VAR PreviousMonthExceptionsWithZero = IF(
        ISBLANK(PreviousMonthExceptions),
        0,
        PreviousMonthExceptions
    )
    VAR YTDExceptions = IF(
        CurrentMonthYear = MaxMonthYear,
        CurrentMonthExceptions + PreviousMonthExceptionsWithZero,
        CurrentMonthExceptions
    )
    RETURN YTDExceptions

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