Result Not DIsplay Uisng NextMonth Function

[yogi678_1]

Hi, I have a dataset with date type column called Contract End Date. I have created a new column called Extracted_Contract_End_Date to extract the date in MMM-YYYY format.  Then I have created a measure from the Contract End Date using NextMonth function hoping to get the total count for next month contract that will expire.


However, the measure is not able to display the result in the matrix table.

The DAX code for the measure is as below:

Contract_Expire_Next_Month = CALCULATE(COUNT(Sheet1[Contract End Date]), NEXTMONTH(Sheet1[Contract End Date]))

Can anyone advise me please? Thank you.

[aschkan · Accepted solution]

If I understand correctly, you need the count of expiring contracts next month for each record in your table, right?
NEXTMONTH() will get you a column of all dates in the following month.
Maybe something like this would help:

Expiring Contracts Next Month = 
VAR CurrentDate = MAX(Tabelle[End Date])  // To get the date in context
VAR FirstDayNextMonth = EOMONTH(CurrentDate, 0) + 1
VAR LastDayNextMonth = EOMONTH(CurrentDate, 1)
VAR ContractCount = CALCULATE(
    COUNTROWS(Tabelle),
    ALL(Tabelle[EndMonth]),  // Remove the filter context imposed by EndMonth, else the measure would not work, when you add EndMonth.
    Tabelle[End Date] >= FirstDayNextMonth,
    Tabelle[End Date] <= LastDayNextMonth
)
RETURN
IF(ISBLANK(ContractCount), 0, ContractCount)

[yogi678_1]

My expected result in the matrix table is like the screenshot below:

[aschkan · Accepted solution]

If I understand correctly, you need the count of expiring contracts next month for each record in your table, right?
NEXTMONTH() will get you a column of all dates in the following month.
Maybe something like this would help:

Expiring Contracts Next Month = 
VAR CurrentDate = MAX(Tabelle[End Date])  // To get the date in context
VAR FirstDayNextMonth = EOMONTH(CurrentDate, 0) + 1
VAR LastDayNextMonth = EOMONTH(CurrentDate, 1)
VAR ContractCount = CALCULATE(
    COUNTROWS(Tabelle),
    ALL(Tabelle[EndMonth]),  // Remove the filter context imposed by EndMonth, else the measure would not work, when you add EndMonth.
    Tabelle[End Date] >= FirstDayNextMonth,
    Tabelle[End Date] <= LastDayNextMonth
)
RETURN
IF(ISBLANK(ContractCount), 0, ContractCount)

[v-xuxinyi-msft · Accepted solution]

Hi @aschkan 

You are on the right track. It worked like a charm!

Depending on @alvin1999 needs, the approach can be modified like this:

Expiring Contracts Next Month = 
VAR CurrentDate = MAX('Table'[Extracted_Contract End Date])  // To get the date in context
VAR FirstDayNextMonth = EOMONTH(CurrentDate, 0) + 1
VAR LastDayNextMonth = EOMONTH(CurrentDate, 1)
VAR ContractCount = CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),  // Remove the filter context imposed by EndMonth, else the measure would not work, when you add EndMonth.
    'Table'[Extracted_Contract End Date] >= FirstDayNextMonth,
    'Table'[Extracted_Contract End Date] <= LastDayNextMonth
)
RETURN
IF(ISBLANK(ContractCount), 0, ContractCount)

Notes: By the way I am following the steps below to set the Extracted_Contract End Date because after my testing it may cause an error if I use the FORMAT function.

if I use the FORMAT function

Best Regards,
Yulia Xu

[alvin1999]

When we remove the End Date column in the matrix table, the measure will not work. 
Can I know how to troubleshoot it?