Forum Discussion
Replace Month Values from Numbers to Names
Good morning.
I have a sheet that tells me the sales values I need based on some segmenters.
I have measures of "salesselected", "salesselectedprevious year", "salesselectedprevious month".
I have several segmenters, one of them is "month" from the month column and another is "year" from the year column.
If I apply the segmenters, all the values of the cards work perfectly.
I have added a table assigning the name of the month with its corresponding value, relating it to the table that contains the value "month", but that always affects the measure "salesselectedpreviousmonth", always showing a value "0" or "blank".
All I need is that in the "month" segmenter instead of showing "1" "January" appears and that everything continues to work the same.
Thanks in advance.
Hi
- Data Model: You have a SalesData table (month, year, sales) and a MonthNames table mapping month (1) to names (January). Ensure a one-to-many relationship between MonthNames[month] and SalesData[month].
- Segmenter: Use MonthNames[MonthName] in the segmenter to show names like "January".
- Fix salesselectedpreviousmonth: If the measure returns 0 or blank, it’s likely due to filter context issues. Check the DAX, e.g.:
salesselectedpreviousmonth =
CALCULATE(
[salesselected],
PREVIOUSMONTH('SalesData'[DateColumn]) -- Requires a date column
)
If no date column exists, calculate manually:
salesselectedpreviousmonth =
VAR CurrentMonth = SELECTEDVALUE('MonthNames'[month])
VAR CurrentYear = SELECTEDVALUE('SalesData'[year])
VAR PrevMonth = IF(CurrentMonth = 1, 12, CurrentMonth - 1)
VAR PrevYear = IF(CurrentMonth = 1, CurrentYear - 1, CurrentYear)
RETURN
CALCULATE(
[salesselected],
'SalesData'[month] = PrevMonth,
'SalesData'[year] = PrevYear
)
- Use a Calendar Table: For better results, create a calendar table with Date, MonthNumber, MonthName, and Year. Relate it to SalesData using a calculated date (e.g., DATE(SalesData[year], SalesData[month], 1)). Then use:
salesselectedpreviousmonth =
CALCULATE(
[salesselected],
PREVIOUSMONTH('Calendar'[Date])
)
Test: Verify that selecting "January" in the segmenter shows correct values for all measures, including salesselectedpreviousmonth.
5 Replies
Hi
- Data Model: You have a SalesData table (month, year, sales) and a MonthNames table mapping month (1) to names (January). Ensure a one-to-many relationship between MonthNames[month] and SalesData[month].
- Segmenter: Use MonthNames[MonthName] in the segmenter to show names like "January".
- Fix salesselectedpreviousmonth: If the measure returns 0 or blank, it’s likely due to filter context issues. Check the DAX, e.g.:
salesselectedpreviousmonth =
CALCULATE(
[salesselected],
PREVIOUSMONTH('SalesData'[DateColumn]) -- Requires a date column
)
If no date column exists, calculate manually:
salesselectedpreviousmonth =
VAR CurrentMonth = SELECTEDVALUE('MonthNames'[month])
VAR CurrentYear = SELECTEDVALUE('SalesData'[year])
VAR PrevMonth = IF(CurrentMonth = 1, 12, CurrentMonth - 1)
VAR PrevYear = IF(CurrentMonth = 1, CurrentYear - 1, CurrentYear)
RETURN
CALCULATE(
[salesselected],
'SalesData'[month] = PrevMonth,
'SalesData'[year] = PrevYear
)
- Use a Calendar Table: For better results, create a calendar table with Date, MonthNumber, MonthName, and Year. Relate it to SalesData using a calculated date (e.g., DATE(SalesData[year], SalesData[month], 1)). Then use:
salesselectedpreviousmonth =
CALCULATE(
[salesselected],
PREVIOUSMONTH('Calendar'[Date])
)
Test: Verify that selecting "January" in the segmenter shows correct values for all measures, including salesselectedpreviousmonth.
Create this custom column in the query editor
Date.ToText(#date(2025, [month number column], 1), "MMMM")The year and day elements can be different ones. The important thing is the columns returns a date with the correct month before being converted to a text month. Should be uppercase M as m in the query editor refers to minutes instead of months.
Thank you for being part of the Microsoft Fabric Community.
As highlighted by danextian , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.
Best Regards,
Community Support Team _ C Srikanth.I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!Best Regards,
Community Support Team _ C Srikanth.We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.
