Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 meas...
  • aschkan's avatar
    aschkan
    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)

     

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