cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Create Latest and previous month marker

Hi

I'm trying to solve what i think should be a pretty simple problem, but it's beating me!

I'm trying to create a calculated column which basically returns whether a date is the "latest" month or it's the "previous" month.

I have the below code which doesn't work because the previousmonth function needs to reference a column. Does anyone have any ideas how to work around this??

if(Date_table[Month_end] = MAX(Date_table[Month_end]), "Latest", if(Date_table[Month_end] = PREVIOUSMONTH(MAX(Date_table[Month_end])), "Previous","NA"))
1 ACCEPTED SOLUTION
Solution Supplier

@Anonymous

Try this code - I just tweaked your original code, but you need to add one more calculated column called PrevMonth. Check this out.

1.

``Prev Month = PREVIOUSMONTH(Date_table[Month_end])``

2.

``Month Marker = if(Date_table[Month_end] = MAX(Date_table[Month_end]), "Latest", if(Date_table[Month_end] = max(Date_table[Prev Month]), "Previous","NA"))``

Let me know if this works for you. Many Thanks

6 REPLIES 6
Anonymous
Not applicable

@Manoj_Nair  many thanks. I'd actually tried this previously and it didn't work, but clearly i'd made a mistake somewhere and on second go it works perfectly!

Helper V
Column = IF(MONTH(calender[Date])=MONTH(TODAY()),"Latest","Previous")

is this you are excepting???

Anonymous
Not applicable

Unfortunately i can't use the today function as the dates i'm using are month end dates. So for instance my latest dates in the column are currently 31/12/2022 and 30/11/2022. I would like to mark the December date as "Latest" and the November date as "Previous"

Helper V

Provide some sample data(by removing sensitive data), so that its easy to get betetr answers..

Anonymous
Not applicable

So i'm trying to achieve this

 Month end Month marker 30/09/2022 NA 31/10/2022 NA 30/11/2022 Previous 31/12/2022 Latest
Solution Supplier

@Anonymous

Try this code - I just tweaked your original code, but you need to add one more calculated column called PrevMonth. Check this out.

1.

``Prev Month = PREVIOUSMONTH(Date_table[Month_end])``

2.

``Month Marker = if(Date_table[Month_end] = MAX(Date_table[Month_end]), "Latest", if(Date_table[Month_end] = max(Date_table[Prev Month]), "Previous","NA"))``

Let me know if this works for you. Many Thanks

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors