How to show Current Month and Previous Month values if user selects Month name

[VamshiGoud]

Hello ,

I havea  requirement where i want to show Current month and Previous month Sales Amount in table visual. However when user filters the year and month name slicer those CM, PM values should change accordingly.

Ex : If Month slicer Mar selected CM value should show N Month (Mar) amount and PM should show N-1 Month (Feb amount). Same way if Feb selected CM should show Feb and PM should show Jan. It means PM measure should always show N-1 Month values as per the selected Month from Month slicers 

Note : It should work as per the financial year. Financial year starts from Apr-Mar

Sample Data

Date	Year	Month	Product	Values
01 October 2024	2024	Oct	A	20
01 November 2024	2024	Nov	B	10
01 December 2024	2024	Dec	C	30
01 January 2025	2025	Jan	A	20
01 February 2025	2025	Feb	B	10
01 March 2025	2025	Mar	C	30

Appricate your help. Thanks in Advance

[VamshiGoud]

It's working as i expected . Thanks a Lot 🙂

[danextian]

Hi @VamshiGoud 

Time intelligence calculations should be relatively simple with a separate dates dimension table that's been marked as a dates table. The dates table is connected to your fact table in a single direction one-to-many relationship.

You can use PREVIOUSMONTH to get the prior month's value relative the current row month or whatever is selected in the slicer.

Total Revenue Previous Month =
CALCULATE (
    [Total Revenue],
    PREVIOUSMONTH ( Dates[Date] ),
    REMOVEFILTERS ( Dates )
)

REMOVEFILTERS is not necessary if the dates table has been marked as such.  -https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

Please see the attached sample pbix.

[grazitti_sapna · Accepted solution]

Hi @VamshiGoud , 

You can achieve this by creating two DAX measures:

1. Current Month Sales (CM)

2. Previous Month Sales (PM)

CM Sales = 
VAR SelectedMonth = SELECTEDVALUE('Table'[Month])
VAR SelectedYear = SELECTEDVALUE('Table'[Year])
RETURN 
CALCULATE(
SUM('Table'[Values]),
'Table'[Month] = SelectedMonth,
'Table'[Year] = SelectedYear
)

Another one:- 

PM Sales = 
VAR SelectedMonth = SELECTEDVALUE('Table'[Month])
VAR SelectedYear = SELECTEDVALUE('Table'[Year])

-- Create a mapping for financial months to numeric order
VAR MonthOrder = 
SWITCH(SelectedMonth,
"Apr", 1, "May", 2, "Jun", 3, "Jul", 4, "Aug", 5, "Sep", 6,
"Oct", 7, "Nov", 8, "Dec", 9, "Jan", 10, "Feb", 11, "Mar", 12
)

-- Find the Previous Month and adjust the Year if needed
VAR PrevMonthOrder = IF(MonthOrder = 1, 12, MonthOrder - 1)
VAR PrevYear = IF(MonthOrder = 1, SelectedYear - 1, SelectedYear)

-- Find Previous Month Name
VAR PrevMonthName = 
SWITCH(PrevMonthOrder,
1, "Apr", 2, "May", 3, "Jun", 4, "Jul", 5, "Aug", 6, "Sep",
7, "Oct", 8, "Nov", 9, "Dec", 10, "Jan", 11, "Feb", 12, "Mar"
)

RETURN
CALCULATE(
SUM('Table'[Values]),
'Table'[Month] = PrevMonthName,
'Table'[Year] = PrevYear
)

🌟 I hope this solution helps you unlock your Power BI potential!

If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!