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.

    Create simple DAX Measure.pbix22 KB
Anonymous's avatar
Anonymous
Not applicable
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.

Create simple DAX Measure.pbix22 KB
  • arunh100's avatar
    arunh100
    Frequent Visitor
    1 year ago

    Thanks Jarvis Tang for sharingh the PBIX file, it worked successfully

  • arunh100's avatar
    arunh100
    Frequent Visitor
    1 year ago

    Hi Jarvis Tang

     

    There is one change required for the input column: CreatedMthYr column which is of Text type eg: 2024-10,2024-11 and 2024-12 instead of Integer

    The above DAX for YTD Measure is failing and returning blank in Previous Month Exceptions when we have values:

    CreatedMthYr Status YTD Measure

    2024-12 Open               CurrentMonthExceptions(36)

    2024-12 OnHold           CurrentMonthExceptions(39)

    2024-12 Completed      CurrentMonthExceptions(32)

     

    It is not adding value for 2024-12 + 2024-11 and displaying against 2024-12 as Previous Month Exceptions is retuning blank in the above expression

     

    YTDMeasure =
    VAR MaxMonthYear = (CALCULATE(MAXX(ALL('Exceptions'), 'Exceptions'[Created Month Year])))
    VAR CurrentMonthYear = (SELECTEDVALUE('Exceptions'[Created Month Year]))
    VAR CurrentMonth = VALUE(RIGHT(CurrentMonthYear,2))
    VAR CurrentYear = VALUE(LEFT(CurrentMonthYear,4))
    VAR PreviousMonthYear = IF(
        CurrentMonth = 1,
        (CurrentYear - 1) * 100 + 12,
        CurrentYear * 100 + (CurrentMonth - 1))
    VAR PrevMthYr=LEFT(CONVERT(PreviousMonthYear,STRING),4)&"-"&RIGHT(CONVERT(PreviousMonthYear,STRING),2)
    VAR CurrentMonthExceptions = CALCULATE(
        COUNT(Exceptions[Task ID]),
        VALUE('Exceptions'[Created Month Year]) = VALUE(CurrentMonthYear)
    )
    VAR PreviousMonthExceptions = CALCULATE(
        COUNT(Exceptions[Task ID]),
        ('Exceptions'[Created Month Year]) = (PrevMthYr)
    )
    VAR PreviousMonthExceptionsWithZero = IF(
        ISBLANK(PreviousMonthExceptions),
        0,
        PreviousMonthExceptions
    )
    VAR CurrentMonthExceptionsWithZero = IF(
        ISBLANK(CurrentMonthExceptions),
        0,
        CurrentMonthExceptions
    )
    VAR YTDExceptions = IF(
        CurrentMonthYear = MaxMonthYear,
        CurrentMonthExceptions + PreviousMonthExceptions,
       
        CurrentMonthExceptions
    )
    RETURN YTDExceptions

     

    Regards

    Arun