cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Based on date slicer- display current month and previous month names

Hi All,

I have calender[date] column which is being used in slicer hierarchy level based on selection it should display current month& year, previous month& year  in card label.

example screenshot :

if user selected in slicer  Jan'2023 then

current month_year = Jan 2023

pre month_Year = Dec 2022

1 ACCEPTED SOLUTION
Super User

Hi @MSuser5 ,

1-Create a calculated column as:

YearMonth = CONVERT(FORMAT(DateTable[Date],"YYYYMM"),INTEGER)

2-Create another calculated column as:
YearMonthOrder = RANKX (
DateTable,
DateTable[YearMonth],
,
ASC,
DENSE
)

3-CurrentMonth Measure is as below:
CurrentMonth =
CALCULATE (
MIN ( DateTable[MonthName] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthOrder] = MAX ( DateTable[YearMonthOrder] )
)
)

4-PrevMonth Measure is:
PrevMonth =
CALCULATE (
MIN ( DateTable[MonthName] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthOrder]
MAX ( DateTable[YearMonthOrder] ) - 1
)
)

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran
2 REPLIES 2
Super User

Hi @MSuser5 ,

1-Create a calculated column as:

YearMonth = CONVERT(FORMAT(DateTable[Date],"YYYYMM"),INTEGER)

2-Create another calculated column as:
YearMonthOrder = RANKX (
DateTable,
DateTable[YearMonth],
,
ASC,
DENSE
)

3-CurrentMonth Measure is as below:
CurrentMonth =
CALCULATE (
MIN ( DateTable[MonthName] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthOrder] = MAX ( DateTable[YearMonthOrder] )
)
)

4-PrevMonth Measure is:
PrevMonth =
CALCULATE (
MIN ( DateTable[MonthName] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthOrder]
MAX ( DateTable[YearMonthOrder] ) - 1
)
)

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran
Helper II

Thank you so much it's working as expected.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors