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:

CreatedMthYrStatusCount of Task ID
202410Open9
202410OnHold10
202410Completed20
202411Open20
202411OnHold30
202411Completed39
202412Open36
202412OnHold39
202412Completed

32

 

 

 

 

 

Need to create Exceptions Count with below output:

 

CreatedMthYrStatusExceptions Count
202410Open9
202410OnHold10
202410Completed20
202411Open20
202411OnHold30
202411Completed39
202412Open56
202412OnHold78
202412Completed71

 

Here Data for Exceptions Count for CreatedMthYr=202412 (Maximum CreatedYearMth), it should add the Exceptions Count for CreatedMthYr=202412 and CreatedMthYr=202412 for each Status. And for other CreatedYrMth it should the respective Exception Counts for that CreatedYrMth

We need to create DAX measure to achieve this functionality.

I tried creating DAX MEasure:

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(MOD(VALUE(CurrentMonthYear),100)=1,(CurrentYear-1)*100+12,INT(DIVIDE(VALUE(CurrentMonthYear),100,0))*100+(MOD(VALUE(SELECTEDVALUE(Exceptions[CreatedMthYr])),100)-1))
var CurrentMonthExceptions=CALCULATE(COUNT(Exceptions[Task ID]),'Exceptions'[CreatedMthYr]=VALUE(SELECTEDVALUE(Exceptions[CreatedMthYr])))
var PreviousMonthExceptions=CALCULATE(COUNT(Exceptions[Task ID]),'Exceptions'[CreatedMthYr]=VALUE(PreviousMonthYear))
var PreviousMonthExceptionswithZero=IF(ISBLANK(PreviousMonthExceptions),0,PreviousMonthExceptions)
var YTDExceptions=  
IF((CurrentMonthYear)=(MaxMonthYear),((CurrentMonthExceptions+PreviousMonthExceptionswithZero)),CurrentMonthExceptions)
RETURN YTDExceptions
 
However the Measure is not returning data for PreviousMonthExceptions for all CreatedMthYr and Statuses..but we do hve data as per the sample data provided. As a result we are not getting correct Exception Count for CreatedMthYr=202412
  • 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.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      Thanks Jarvis Tang for sharingh the PBIX file, it worked successfully

    • arunh100's avatar
      arunh100
      Frequent Visitor
      1 year ago

      Hi Jarvis Tang

       

      There is one change required for the input column: CreatedMthYr column which is of Text type eg: 2024-10,2024-11 and 2024-12 instead of Integer

      The above DAX for YTD Measure is failing and returning blank in Previous Month Exceptions when we have values:

      CreatedMthYr Status YTD Measure

      2024-12 Open               CurrentMonthExceptions(36)

      2024-12 OnHold           CurrentMonthExceptions(39)

      2024-12 Completed      CurrentMonthExceptions(32)

       

      It is not adding value for 2024-12 + 2024-11 and displaying against 2024-12 as Previous Month Exceptions is retuning blank in the above expression

       

      YTDMeasure =
      VAR MaxMonthYear = (CALCULATE(MAXX(ALL('Exceptions'), 'Exceptions'[Created Month Year])))
      VAR CurrentMonthYear = (SELECTEDVALUE('Exceptions'[Created Month Year]))
      VAR CurrentMonth = VALUE(RIGHT(CurrentMonthYear,2))
      VAR CurrentYear = VALUE(LEFT(CurrentMonthYear,4))
      VAR PreviousMonthYear = IF(
          CurrentMonth = 1,
          (CurrentYear - 1) * 100 + 12,
          CurrentYear * 100 + (CurrentMonth - 1))
      VAR PrevMthYr=LEFT(CONVERT(PreviousMonthYear,STRING),4)&"-"&RIGHT(CONVERT(PreviousMonthYear,STRING),2)
      VAR CurrentMonthExceptions = CALCULATE(
          COUNT(Exceptions[Task ID]),
          VALUE('Exceptions'[Created Month Year]) = VALUE(CurrentMonthYear)
      )
      VAR PreviousMonthExceptions = CALCULATE(
          COUNT(Exceptions[Task ID]),
          ('Exceptions'[Created Month Year]) = (PrevMthYr)
      )
      VAR PreviousMonthExceptionsWithZero = IF(
          ISBLANK(PreviousMonthExceptions),
          0,
          PreviousMonthExceptions
      )
      VAR CurrentMonthExceptionsWithZero = IF(
          ISBLANK(CurrentMonthExceptions),
          0,
          CurrentMonthExceptions
      )
      VAR YTDExceptions = IF(
          CurrentMonthYear = MaxMonthYear,
          CurrentMonthExceptions + PreviousMonthExceptions,
         
          CurrentMonthExceptions
      )
      RETURN YTDExceptions

       

      Regards

      Arun

  • 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

       

       

  • 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

  • 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.