Forum Discussion
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
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.
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. 🙂
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 PlaylistAfter 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.
