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.

Dangar332's avatar
Dangar332
Resident Rockstar
1 year ago

Hi, arunh100 

please find below attached .pbix file 

Best Regards,
Dangar

 

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.