Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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

@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

 

 

View solution in original post

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!

sudhav
Helper V
Helper V

Column = IF(MONTH(calender[Date])=MONTH(TODAY()),"Latest","Previous")
 
is this you are excepting???
 
 

sudhav_0-1675676071169.png

 

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"

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 endMonth marker
30/09/2022NA
31/10/2022NA
30/11/2022Previous
31/12/2022Latest

@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

 

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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