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

previous month Revenue to the Current month

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.

 

MonthRevenuePrevious Month Revenue
Jan100 
Feb200100
Mar300200
Apr400300
May500400
Jun600500
Jul700600
Aug800700
Sep900800
Oct1000900
Nov11001000
Dec12001100

 

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.

 

Previous Month Revenue =
VAR CurrentMonth = MAX('Core Event Reason List'[Fiscal Month])
VAR PreviousMont=
    CALCULATE(
        MAX('Core Event Reason List'[Fiscal Month]),
        FILTER(ALL('Core Event Reason List'), 'Core Event Reason List'[Fiscal Month] < CurrentMonth)
    )
VAR PreviousMonthRevenue =
    CALCULATE(
        SUM('Weekly People Flash'[FTE WFP]) +
        [FTE for Return to Work] +
        [FTE for Leave of Absence] -
        [FTE for Termination],
        'Core Event Reason List'[Fiscal Month] = PreviousMont
    )
RETURN PreviousMonthRevenue
 
3. This isn't working.
RanjanThammaiah_6-1694757715214.png

 

Kindly provide if there are any possible solution.

 

2 REPLIES 2
isjoycewang
Super User
Super User

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

 

isjoycewang_0-1694760249983.png

 

DataNinja777
Super User
Super User

Hi @RanjanThammaiah 

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] ) )

 

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors