Forum Discussion

blindeye's avatar
blindeye
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Identify dates in the last/previous month

I'm trying to create a column that will identify a date that is in the month prior to the current month.

 

I have a calculated column that returns a "Yes" if the date is in the current month as follows:

 

Closed This Month = IF ( YEAR ( 'Ops Data'[Closed Date] ) = YEAR ( TODAY () ) && MONTH ('Ops Data'[Closed Date] ) = MONTH ( TODAY () ), "Yes", "" )

 

What I want is a formula that will count the number of dates the month before and also take account of the change of year (so in January 2018 it will count the number of December 2017 dates, etc.)

 

Hopefully this is a relatively simple request. Any ideas?

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi blindeye,

     

    Please try this measure:

    Count Dates =
IF (
    MONTH ( TODAY () ) = 1,
    CALCULATE (
        COUNT ( 'Ops Data'[Closed Date] ),
        FILTER (
            'Ops Data',
            'Ops Data'[Closed Date].[Year]
                = YEAR ( TODAY () ) - 1
                && 'Ops Data'[Closed Date].[MonthNo] = 12
        )
    ),
    CALCULATE (
        COUNT ( 'Ops Data'[Closed Date] ),
        FILTER (
            'Ops Data',
            'Ops Data'[Closed Date].[Year] = YEAR ( TODAY () )
                && 'Ops Data'[Closed Date].[MonthNo]
                    = MONTH ( TODAY () ) - 1
        )
    )
)

    Best regards,
    Yuliana Gu

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi blindeye,

     

    Please try this measure:

    Count Dates =
IF (
    MONTH ( TODAY () ) = 1,
    CALCULATE (
        COUNT ( 'Ops Data'[Closed Date] ),
        FILTER (
            'Ops Data',
            'Ops Data'[Closed Date].[Year]
                = YEAR ( TODAY () ) - 1
                && 'Ops Data'[Closed Date].[MonthNo] = 12
        )
    ),
    CALCULATE (
        COUNT ( 'Ops Data'[Closed Date] ),
        FILTER (
            'Ops Data',
            'Ops Data'[Closed Date].[Year] = YEAR ( TODAY () )
                && 'Ops Data'[Closed Date].[MonthNo]
                    = MONTH ( TODAY () ) - 1
        )
    )
)

    Best regards,
    Yuliana Gu

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Try this for identifying whether the date is prior to the current month or not.

     

    =IF('Ops Data'[Closed Date]<=EOMONTH('Ops Data'[Closed Date],-1),"Yes","No")

     

    Hope this helps.

    • blindeye's avatar
      blindeye
      Icon for Advocate II rankAdvocate II
      8 years ago

      Thank you both for your responses! Haven't had a chance to try out yet, but will do so ASAP.

      • blindeye's avatar
        blindeye
        Icon for Advocate II rankAdvocate II
        8 years ago

        Thanks Yuliana, your measure did exactly what I needed :smileyhappy: