Forum Discussion
Result Not DIsplay Uisng NextMonth Function
- 2 years ago
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) - Anonymous2 years ago
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
My expected result in the matrix table is like the screenshot below:
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)
- Anonymous2 years agoNot applicable
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 - alvin19992 years agoFrequent Visitor
When we remove the End Date column in the matrix table, the measure will not work.
Can I know how to troubleshoot it?