Forum Discussion
arunh100
1 year agoFrequent Visitor
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...
- Anonymous1 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 YTDExceptionsBest Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
1 year agoNot applicable
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
1 year agoFrequent Visitor
Thanks Jarvis Tang for sharingh the PBIX file, it worked successfully