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
NelsonDíaz1
Frequent Visitor

Current Month - Previous Month

Hello.

I have a table like this

Month         MonthNumber     Sales

Jenuary              1                     X

February            2                     X

March                3                     X

 

I need to create a matrix that shows the sales of the current month as default, but if I choose another month, shows the selected month.

 

I am struggling with the selection. For the current month I am using Month(Today()), and for the selected month SELECTEDVALUE(Table[Month])

 

On separated, Month(Today()) shows 8, and for the SELECTEDVALUE it shows the name of the month. I know, it is not the same because of the format, but I could not solve this, and I need some help.

3 REPLIES 3
parry2k
Super User
Super User

@NelsonDíaz1 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

 

Add Date Dimension
Importance of Date Dimension
Time Intelligence Playlist

 

After this is done, the calculations will be super easy using Time intelligence functions like PREVIOUSMONTH etc, and also this is the right approach and the best practice. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

kameronyork
Resolver I
Resolver I

If you're attempting to compared MONTH(TODAY()) with a month column where's it's spelled out, it would be best to use the FORMAT function to change the 8 from the MONTH output to the correct format.

 

if a fully spelled out month is your desired comparison it would be best to use a function like:

FORMAT( MONTH(TODAY()), "mmmm")

The amount of M's in the second argument determines the length. See this article for more info:  Format Function Microsoft Learn 

 

Getting the value of the previous month could be accomplished with a CALCULATE function:

CALCULATE(  SUM( TABLE[Sales]), TABLE[Month] = FORMAT( MONTH(TODAY()) -1), "mmmm")

Subtracting inside the FORMAT function will help it to be dynamic without using another measure!

 

Thanks! I hope this helps.  If it has, please accept it as the solution. 🙂

foodd
Super User
Super User

Please create a pbix file that contains some sample data but still reflects your data model (tables, relationships, calculated columns, and measures), upload the pbix to Onedrive or Dropbox, and share the link.

Please use Excel to create the sample data instead of the manual input method share the xlsx as well.

 

Describe the expected results based on the sample you provide.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.