Create simple DAX Measure

[arunh100]

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	OnHold	30
202411	Completed	39
202412	Open	36
202412	OnHold	39
202412	Completed	32

Need to create Exceptions Count with below output:

CreatedMthYr	Status	Exceptions Count
202410	Open	9
202410	OnHold	10
202410	Completed	20
202411	Open	20
202411	OnHold	30
202411	Completed	39
202412	Open	56
202412	OnHold	78
202412	Completed	71

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 · Accepted solution]

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]

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

[arunh100]

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

[Dangar332]

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]

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]

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.

[Kedar_Pande]

@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]

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

[DataNinja777]

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]

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