Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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] ) )
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |