how to find data up to the end of last month

[sy898661]

Hi all!

I am trying to set up a way to see my data that is up to the end of last month (not specifically last month, but in general the month prior to the current month) if that makes sense...

My data is set up so that the WIP Completion month for each line shows as the 1st of each month (so even if it was completed 15-March it will show as 1-March)

and for example for today, I would want to show everything that happened before 1-April.... next month I will want to show everything that happened before 1-May, etc

I tried the following code but it is still including data points from this month, what should I do?

End of Month Data = IF(AND(NOT ISBLANK('Table'[WIP Completion Month]), 'Table'[WIP Completion Month] < TODAY()), "Y", BLANK())

Thank you so much!! 🙂

[Greg_Deckler · Accepted solution]

Would be easier if could see some example data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Is your WIP Completion Month a date or a month number or month text? If it is a month number, then you would need MONTH(TODAY()) instead of TODAY() for example.

If it is a true date, I would do something like:

End of Month Data = 
  IF(
    AND(
      NOT(
        ISBLANK('Table'[WIP Completion Month])
      ),
      'Table'[WIP Completion Month] < DATE(YEAR(TODAY()),MONTH(TODAY()),1)
    ), 
    "Y", 
    BLANK()
   )

[sy898661]

@Greg_Deckler that worked perfectly thank you!!

[MattAllington]

There are various ways to do this.  I prefer a calcualted column

You should create a calendar table first. https://exceleratorbi.com.au/power-pivot-calendar-tables/

Then add a calculated column in the calendar table. There are many ways to write this too, but basically you need to check if today's date is in a completed month or not.  Something like this should work.

Column =
VAR Today = TODAY ()
VAR Year = YEAR ( today )
VAR Month = MONTH ( today )
RETURN
    IF (
        'Calendar'[Date] >= DATE ( Year, Month, 1 ),
        "Incomplete",
        "Fully Complete"
    )