Forum Discussion
Result Not DIsplay Uisng NextMonth Function
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.
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)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
4 Replies
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)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