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.

DataNinja777's avatar
DataNinja777
Super User
1 year ago

Hi arunh100 ,

 

The requirement can be addressed by creating a DAX measure that calculates the exceptions count based on the rules provided. The measure handles the maximum CreatedMthYr by summing the exceptions count for the maximum month and its previous month for each status. For other CreatedMthYr values, it directly returns the count for that month. Below is the optimized DAX code:

Exceptions Count =
VAR MaxMonthYear =
    MAXX(ALL('Exceptions'), 'Exceptions'[CreatedMthYr])
VAR CurrentMonthYear =
    SELECTEDVALUE('Exceptions'[CreatedMthYr])
VAR PreviousMonthYear =
    IF(
        MOD(CurrentMonthYear, 100) = 1,
        (INT(DIVIDE(CurrentMonthYear, 100)) - 1) * 100 + 12,
        CurrentMonthYear - 1
    )
VAR CurrentMonthExceptions =
    CALCULATE(
        SUM('Exceptions'[Count of Task ID]),
        'Exceptions'[CreatedMthYr] = CurrentMonthYear
    )
VAR PreviousMonthExceptions =
    CALCULATE(
        SUM('Exceptions'[Count of Task ID]),
        'Exceptions'[CreatedMthYr] = PreviousMonthYear
    )
VAR Result =
    IF(
        CurrentMonthYear = MaxMonthYear,
        CurrentMonthExceptions + PreviousMonthExceptions,
        CurrentMonthExceptions
    )
RETURN
    Result

 

This measure begins by determining the maximum CreatedMthYr in the dataset using the MaxMonthYear variable. The CurrentMonthYear variable retrieves the currently selected CreatedMthYr within the context of the visual, while the PreviousMonthYear variable calculates the preceding month, accounting for the year rollover. The measure computes the exceptions count for the current month using CurrentMonthExceptions and for the previous month using PreviousMonthExceptions. Finally, it returns the sum of the counts for the maximum CreatedMthYr and its previous month, or simply the current month count for other CreatedMthYr values. This logic ensures the output aligns with the specified requirements.

 

Best regards,

  • arunh100's avatar
    arunh100
    Frequent Visitor
    1 year ago

    Hi DataNinja,

     

    I am getting incorrect value for CreatedMthYr=202412 for all Statuses , the last if condition is getting evaluated to false and it is not adding the CurrentMonthExceptions and Previous Month Exceptions, it is simply displaying the count only for 202412.

    Please suggest and advise

     

    Regards