Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI All,
I require assistance in ensuring that my DAX formula accurately calculates the Revenue for the previous month in comparison to the current month using a list of month names and revenue.
Month | Revenue | Previous Month Revenue |
Jan | 100 | |
Feb | 200 | 100 |
Mar | 300 | 200 |
Apr | 400 | 300 |
May | 500 | 400 |
Jun | 600 | 500 |
Jul | 700 | 600 |
Aug | 800 | 700 |
Sep | 900 | 800 |
Oct | 1000 | 900 |
Nov | 1100 | 1000 |
Dec | 1200 | 1100 |
I have created 2 DAX to convert the Text to values. When i was getting the error saying
couldn't load the data for this visual MdxSctript(Model) (65,42) Calculation error in measure 'core Event Reason List'[Previous Month Revenue]; Cannot convert value 'Sep' of type text to type number.
1.Create a new table to map text month names to numeric values.
MonthMappingTable =
SUMMARIZE(
'Core Event Reason List',
'Core Event Reason List'[Fiscal Month],
"MonthNumber", SWITCH('Core Event Reason List'[Fiscal Month],
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apr", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sep", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12,
BLANK()
)
)
2.Create the 'Previous Month Revenue' measure using the mapping table.
Kindly provide if there are any possible solution.
Hi @RanjanThammaiah,
Please see attached Exercise File. Feel free to let me know more about your table if any questions.
Try below measure:
Previous Month Revenue =
VAR CurrentMonth = MAX('MonthMappingTable'[MonthNumber])
VAR PreviousMont=
CALCULATE(
MAX('MonthMappingTable'[MonthNumber]),
FILTER(ALL('MonthMappingTable'), 'MonthMappingTable'[MonthNumber] < CurrentMonth)
)
VAR PreviousMonthRevenue =
CALCULATE(
SUM('Core Event Reason List'[Revenue]),
ALL(MonthMappingTable[Fiscal Month]), MonthMappingTable[MonthNumber] = PreviousMont)
RETURN PreviousMonthRevenue
It is better that you create a separate calendar table (dimension table) to take advantage of the time intelligence functions which dax has to offer. It is always recommended to create a data model with star schema with relationships between fact and dimension tables instead of trying to deal everything through your fact table. You can create a calender table by writing a dax formula like below and create a relationship with your fact table date field.
Calendar =
CALENDAR ( MIN ( 'Fact table'[Date] ), MAX ( 'Fact table'[Date] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
101 | |
75 | |
65 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |