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
flaviobdsti
Helper I
Helper I

Get subsequent month name from selected month in calendar

Hello guys!
I have a question for which I haven't found the solution yet. Can you help me?

I have 3 cards on my report screen. When I select any month I would like the first card to show the selected month and the other two the subsequent months from the first card
For the first card I already have the solution, I just drag the month field from the calendar table to the card, but for the others I don't know how to do it.

Example:

If I select JANUARY the first card will show the text JANUARY, the second will show FEBRUARY and the third one MARCH.

flaviobdsti_0-1671743148811.png


Do you know how I can get these values from the calendar filter selection?

Thank you very much in advance

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

Assuming the slicer has been built from the Calendar Table, write these measures

Selected month = format(max(Calendar[Date]),"mmmm")

Selected month + 1 = format(max(Calendar[Date])+1,"mmmm")

Selected month + 2 = format(edate(min(Calendar[Date]),2),"mmmm")

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

AlexisOlson
Super User
Super User

This is a bit hacky, but you can shift by X months like this:

 

= FORMAT ( EOMONTH ( DATEVALUE ( [Month Name] & " 2000"), X), "mmmm" )

 

For X = 2, DATEVALUE transforms "January 2000" into DATE(2000, 1, 1), shifts it forward 2 months using EOMONTH to DATE(2000,3,31), and formats this as "March" using the custom FORMAT string "mmmm".

View solution in original post

8 REPLIES 8
AlexisOlson
Super User
Super User

This is a bit hacky, but you can shift by X months like this:

 

= FORMAT ( EOMONTH ( DATEVALUE ( [Month Name] & " 2000"), X), "mmmm" )

 

For X = 2, DATEVALUE transforms "January 2000" into DATE(2000, 1, 1), shifts it forward 2 months using EOMONTH to DATE(2000,3,31), and formats this as "March" using the custom FORMAT string "mmmm".

Thanks a lot for the help! It worked correctly!

Ashish_Mathur
Super User
Super User

Hi,

Assuming the slicer has been built from the Calendar Table, write these measures

Selected month = format(max(Calendar[Date]),"mmmm")

Selected month + 1 = format(max(Calendar[Date])+1,"mmmm")

Selected month + 2 = format(edate(min(Calendar[Date]),2),"mmmm")

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,
it just didn't work in the month of December, it should change to January, but it kept showing January. For the remaining months it worked correctly.
I ended up using the solution that the other colleague indicated, even so I thank you for his help.

Ensure that the last date in the Calendar table is a December 31, 2023.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Perfect! Thank you again!

You are welcome.  Please mark that reply of mine as the Answer in which i shared the 3 measures.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Done!

Helpful resources

Announcements
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