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.

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

arunh100 

improved version:

Exceptions Count = 
VAR MaxMonthYear = CALCULATE(MAX('Exceptions'[CreatedMthYr]), ALL('Exceptions'))
VAR CurrentMonthYear = SELECTEDVALUE('Exceptions'[CreatedMthYr])
VAR CurrentMonth = MOD(CurrentMonthYear, 100)
VAR CurrentYear = DIVIDE(CurrentMonthYear, 100, 0)
VAR PreviousMonthYear = IF(CurrentMonth = 1, (CurrentYear - 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 PreviousMonthExceptionsWithZero = IF(ISBLANK(PreviousMonthExceptions), 0, PreviousMonthExceptions)
VAR YTDExceptions = IF(CurrentMonthYear = MaxMonthYear, CurrentMonthExceptions + PreviousMonthExceptionsWithZero, CurrentMonthExceptions)
RETURN YTDExceptions
💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn
arunh100's avatar
arunh100
Frequent Visitor
1 year ago

Hi Kedar

 

I tried creating the measure with the improved version shared by you. However, the data for CreatedMthYr=2012112, it is not adding up CurrentMonthExceptions and PreviousMonthExceptions.The If condition is failing and I checked in the MaxMonthYear it is showing 202412 , Kindly suggest and advise.Also I checked the format of all the columns it is set properly

 

Regards