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.

arunh100's avatar
arunh100
Frequent Visitor
1 year ago

Hi Dangar

 

There is one twist. Count of Task ID is not direct column.Its count of Task ID measure. So I am not getting same result as yours. Kindly advise and suggest

 

Regards

Dangar332's avatar
Dangar332
Resident Rockstar
1 year ago

Hi, arunh100 

Amend measures as below

 

Measure = 
var a = CALCULATE(MAX('Table'[CreatedMthYr]),REMOVEFILTERS('Table'[CreatedMthYr]))
var b = MAXX(FILTER(all('Table'),'Table'[CreatedMthYr]<a),'Table'[CreatedMthYr])
var c = CALCULATE([Count of Task ID],'Table'[CreatedMthYr]=b)+[Count of Task ID]
RETURN
SUMX('Table',
IF('Table'[CreatedMthYr]=a,c,[Count of Task ID]))

 



If this not work then please provide the code of measure that are use for "Task ID" count or some sample data with Task ID Column .


Best Regards,
Dangar

 

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